Same Period Last Month with another measure

[KevinRDynasoft]

Hi there,

I have a Total Income and Total Deduction DAX measures and they work fine but I'm trying to show the same values compared to last month.

I'm using these formulas for totals:

Total Income= CALCULATE(SUM('Payroll Report'[Total]),FILTER('Payroll Report','Payroll Report'[Type]=="Income"))

Total Deduction= CALCULATE(SUM('Payroll Report'[Total]),FILTER('Payroll Report','Payroll Report'[Type]=="Deduction"))

------------------------------------------------------------------------------------------------------------------------------------------------

And these formulas for prev month:

Prev Month Income = CALCULATE([Total Income], DATEADD('Payroll Report'[Date], -1, MONTH))

If i insert a table that shows date, total income and prev month income, I can see that prev month income has no data.

It's not a relationship problem because my 'Date' is column inside my payroll report. 'Total' is formatted to currency. 'Total Income' is also formatted to currency.

[amitchandak · Accepted solution]

For time intelligence prefer using date calendar table

Prev Month Income = CALCULATE([Total Income], DATEADD('adte'[Date], -1, MONTH))
or

Prev Month Income = CALCULATE([Total Income], datesmtd(DATEADD('adte'[Date], -1, MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[HotChilli]

Test the dateadd clause to see if it's returning blanks.

[KevinRDynasoft]

Yes. It's returning blank

[HotChilli]

Hopefully it's sorted now.  The dates that DATEADD returns must already exist in the date column you use otherwise it returns blank.  That's why a date table works (This can still return blanks for the same reason but not if you set the date table up properly)