Reporting_date in slicer doesn't not update measures when user change it

[cisamber]

Hi All,

I am using a single select slicer for my reporting_date. This date is a calculated column (EOMONTH) from my calendar date table.

I want to create measures to get my sales for the month, previous month and YTD. The goal is to update all my measures when the users change the slicer.

So far, I have succeed to make the month and YTD works but the previous month measure doesn't work. I have tried many formulas like SELECTVALUE, VALUES, DATEADD, EOMONTH, PREVIOUSMONTH etc. but nothing works. I get blanks.

------------MEASURES WHICH WORKS---------

Mea_Revenue_month = Var selectedValue = SELECTEDVALUE('Calendar'[Reporting_date])
Return
CALCULATE([Mea_Sale],'Calendar'[Reporting_date] = selectedValue)
Mea_Sale = SUM(Manual_adjustments[TimeRecords_SaleValue]) + SUM(TS_TimeSheet[TimeRecords_SaleValue])
------------MEASURES WHICH DOESN'T WORKS---------
Mea_Revenue_Last_month = Var selectedValue = EOMONTH(SELECTEDVALUE('Calendar'[Reporting_date]), - 1)
Return
CALCULATE([Mea_Sale],'Calendar'[Reporting_date] = selectedValue)

Please help!!!!!

[ayan_biswas]

Hi Team, I have Date parameter, based on user selection, I have extracted the first day and last day of month using DAX query. 

Now want to extract measures from another table based on that first and last day date range. Thanks in advance for the help.

TestDateSelection = SELECTEDVALUE('Calendar'[Date])

[MattAllington]

I think you will find eomonth returns a date. You are subtracting one day from the end of month, which for example could be 29th of the month, and then you are finding the end of month again. You need to subtract 1 month, not 1 day 

[cisamber]

Hi Matt, 

Even if I use what you describes, it doesn't work.

DATEADD('Calendar'[Reporting_date],-1,MONTH)

[MattAllington]

Dateadd is an inbuilt time intelligence function that only works when you have a calendar table with a day level granularity and you use the day level date column inside DATEADD. 

[amitchandak]

@cisamber , you can not go beyond deleted dates, unless you use Date/calendar table marked as date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Refer my blogs, I have addressed these

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[cisamber]

Hi @amitchandak , my calendar table is marked as a date table. The formula above don't work.

[v-easonf-msft]

Hi, @cisamber 

Can you provide a screenshot of your slicer?

Did you select a specific date (2021/3/22) in the slicer, or a  time period (2020-3)?

Please share a sample pbix for testing.

Best Regards,
Community Support Team _ Eason