Time intelligence case!

[Anonymous]

Hi Guys,

I have a large data set of monthly data over several years and have calculated last months sales using the following DAX.

Total sales last month = CALCULATE ([Total sales];
                                                   DATEADD (Dates[Date];-1;MONTH ) )

 I have no sales data for any months of January and hence no "sales last month" in february.. For my purpouse I need to move all "Total sales last month" in January to February.. Does anybody have a solution to that?

Date	Total Sales	Total Sales Last Month
December 2015	200	50
January 2016	0	200
February 2016	100	0

(I need to get the 200 in January 2016 down to February 2016)

Would be greatly appreciated!

[Chris99 · Accepted solution]

Hi @Anonymous,

Can you do

Total sales last month = IF

(

   CALCULATE ([Total sales]; DATEADD (Dates[Date];-1;MONTH ) ) = 0;

   CALCULATE ([Total sales]; DATEADD (Dates[Date];-2;MONTH ) );

   CALCULATE ([Total sales]; DATEADD (Dates[Date];-1;MONTH ) );

 )

or were you hoping for something that would work backwards and find the fisrt non zero value?

Chris

[Anonymous]

Hi @Chris99, 

That WOOOOOORKED! Thanks :))

Been struggling for ages!

[Chris99]

@Anonymous- Glad to have helped