Dateadd for incomplete months - problem with the last date

[baravo]

Hi, I am trying to make a comparison of revenues this month vs last month. I want to compare the current month, i.e. 01/07 to 20/07 against 01/06 to 20/06. 

All works fine, until I select the last available day in the slicer. It does not compare the proportional part of the last month, but the whole last month. When I select the last but one date, it works fine.

What can I do to solve it?

This is the DAX function I used:

Revenue LM = CALCULATE([Revenue],DATEADD('Calendar'[Date],-1,MONTH))

Thank you.

[baravo]

Guys, these do not solve my issue. The DATEADD, as well as your suggestions work fine, but the last day of the dataset does not work fine, instead of comparing the same proportion of the month vs previous month, i.e. 1-20/07 vs 1-20/06, it compares 1-20/07 vs 1-30/06, that is the whole month of June. But this happens only when I select the last available date in the slicer, i.e. current date.

The most common issue is this one:

https://community.powerbi.com/t5/Desktop/Dateadd-for-incomplete-months/m-p/540260/highlight/false#M2...

MFelix comments: "When having time intelligence it will get the last day of the month as a maximum value so you are getting the full month if you change your slicer to one day earlier it will give only the 10 days of october.

To work around this you should add your date to the visual filter or report filter and the select the option Relative date filtering and select is in the last X days, months or years."

However, this work around doesn't work to me.

[baravo · Accepted solution]

I solved it!

The problem was that my calendar date was related to another table (invoices) with MIN and MAX date values:

Calendar = CALENDAR(MIN(Invoices[date]),MAX(Invoices[date]))

I changed my Calendar Date to this:

Calendar = CALENDAR(MIN(Invoices[date]),DATE(2021,31,12))

and limited the dates in filter by Relative Date for the last 3 years, so I only see the past till today.

It works!

[V-lianl-msft]

Hi @baravo ,

Refer to this:

https://community.powerbi.com/t5/Desktop/Calculation-for-Last-month-Same-Period/td-p/730183 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@baravo , this should have worked. But when you use month year as view by it start taking month-end date.

Try datesmtd

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

also refer

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e