Time intelligence Help ( Previous Month Calculation)

DAX DAX Calculations
1

Hi All,

I would require your guidance to understand a scenario. Basically i am calculating Previous Month values but it is giving me different results based on the context. So would require so help with respect to the same. Use case is as follows -
I have a fact table with Dates of start of the each month.The Previous month calculation used by me are as follows -

COUNT PM = CALCULATE(Measure);FILTER(all (Facttable);MONTH(Fact[PeriodDate]) = Month(Max(Fact[PeriodDate]))-1 && YEAR(Fact[PeriodDate] )= YEAR(MAX(Fact[PeriodDate]))))

The output of this measures gives me blank result of January every year. It would be great if someone can help put some light on the same.

When is use calculation as below the out put is correct.

Previous Month = CALCULATE(Measure);LASTDATE(DATEADD(Fact[PeriodDate];-1;MONTH)))

Please find attached the image_01 for the above two outputs

Image01
Image01.PNG501×804 23 KB

Now scenario 2 : -

When in my fact table i have dates of end of month i.e 30th or 31st of each month the calculation

Previous Month = CALCULATE(Measure);LASTDATE(DATEADD(Fact[PeriodDate];-1;MONTH)))

Gives me result only at the total level against the date context and not for all the dates. See the attachment image02.image02

Kindly request you to help me with the behavior of the dax in such scenario and provide me with the correct calculation for the same against the date context.

2

@Vishy
Just at first glance, but looks like you do not have a dedicated date table. You need to have a date table in order to use the Time Intelligence functions correctly. When you have something like

…Filter( FACTTABLE)

That should be a warning that you made a wrong turn somewhere. Filter is an iterator, and you really don’t ever want to iterate a fact table because that will kill performance. So try adding in a date table and using that instead of your fact table for dates.

Nick,

Enterprise%20DNA%20Expert%20-%20Small

3

Yes first things first here you need a date table before doing anything else.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000650

I would work through all the video tutorials after this one as well.

Basically if you get the date table in your model and use this then it will fix all of your issue here just like that.

Then all you need to do is use a time intelligence function like DATEADD and this calculation is super easy.

Thanks
Sam

4

Thanks for the inputs I would work through the same and update this post

5

Hi All,

Thanks it helped me resolve the issue