topic Re: Same period last month filter is not working with period/date/month slicer in DAX Commands and Tips

Same period last month filter is not working with period/date/month slicer

PBIlomiddze — Wed, 10 May 2023 04:39:03 GMT

Hi, I have to compare the previous month's sales with the current month's sales.

I have two formulas:

MTD Current month =
TOTALMTD([sales],sales_aggregated[Date]) - shows correct number

MTD Last Month =

CALCULATE(

[Sales],

DATEADD( FILTER( DATESMTD( sales_aggregated[date]),sales_aggregated[date]<MAX(sales_aggregated[date])),-1,MONTH)

) Shows the Correct number for the previous month.

and they both work in the divide formula as well - as a result, they show change %.

But I want to select month, and show the "MTD" vs "MTD Prev month", for example if now is 05.2023, I want to select 03.2023 and formula should show 03.2023 and other 02.2023 but as the slicer is filtering the whole table on dates only for 03.2023, 02.2023 is filtered out and MTD Last month is blank. how can i prevent a blank value on the previous month?

Re: Same period last month filter is not working with period/date/month slicer

amitchandak — Wed, 10 May 2023 05:43:53 GMT

PBIlomiddze , Make sure you are using date table, try measure like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

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

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Same period last month filter is not working with period/date/month slicer

PBIlomiddze — Mon, 15 May 2023 06:03:22 GMT

The main part of the solution was to use Calendar as a filter. more or less the implemented formulas were working - in the end: the solution looks like this.

MTD_Revenue_Total = CALCULATE([Revenue],DATESMTD('Z_Calender'[Date]))

MTD_LM_Revenue_Total = CALCULATE(

[Revenue],

DATEADD(FILTER(DATESMTD(Z_Calender[Date]),Z_Calender[Date]<TODAY()),-1,MONTH)

) - Calendar < today - I have included this part to filter the current month on the dates that are passed - to not compare the latest month with the whole previous month. for other months it will be comparing the whole month to the whole month.

MTD_VS_MTD_LM_Revenue_Total =

IF([1_KPI_MTD_LM_Revenue_Total]=0,BLANK(),

DIVIDE([1_KPI_MTD_Revenue_Total],[1_KPI_MTD_LM_Revenue_Total],0)-1

)