CODEWITHSUNDEEP
exceldatepivot-tablepowerpivot
Bennett Blodinger
Bennett Blodinger

Reputation: 107

PowerPivot % Difference from previous month

When trying to get the percent difference from one month to the next, January (or the first month in the dataset) will always show as blank. How would I go about making Excel look for the previous month of last year?

I am using DateStream as my date table.

Issues and settings:

enter image description here

enter image description here

Upvotes: 0

Views: 1542

Answers (1)

PowerDAX
PowerDAX

Reputation: 142

You would need to add a calculated field to calculate the total for the same period last year...i.e.

TotalSPLY:=CALCULATE(SUM( 'TableName'[Total]), SAMEPERIODLASTYEAR( DateTable[DateKey]))

https://support.office.com/en-sg/article/SAMEPERIODLASTYEAR-Function-DAX-b8f7f423-22f5-470f-abd3-b76a1250bcc1?ui=en-US&rs=en-SG&ad=SG

UPDATE -- (to adhere to the previous month from last year requirement)

=CALCULATE(SUM( 'TableName'[Total]), DATESBETWEEN( 'DateTable'[DateKey], 

IF( DATE( YEAR( EOMONTH(  MIN( 'DateTable'[DateKey]),-13) ), MONTH( EOMONTH( MIN( 'DateTable'[DateKey]),-13) ), 1 ) < CALCULATE ( MIN( 'DateTable'[DateKey]), ALL('DateTable') ), CALCULATE ( MIN( 'DateTable'[DateKey]), ALL('DateTable') ),             
       DATE( YEAR( EOMONTH(  MIN( 'DateTable'[DateKey]),-13) ), MONTH( EOMONTH( MIN('DateTable'[DateKey]),-13) ), 1 ) ),
IF ( EOMONTH( MIN( 'DateTable'[DateKey]), -13) < EOMONTH( CALCULATE ( MIN( 'DateTable'[DateKey]), ALL('Date') ), 0 ), EOMONTH( CALCULATE ( MIN( 'DateTable'[DateKey]), ALL('DateTable') ), 0 ), 
EOMONTH( MIN( 'DateTable'[DateKey]), -13) ) 
) )

Upvotes: 1

Related Questions