Default Month Filter along with MoM

[Manikantans2]

Greetings, 

I have a 'Period' column and a 'Revenue' column in my table. I have set up a calendar table as well. 

I am displaying previous month revenue (Previous of the month selected in the month slicer) as a card using the below DAX:

Last Month Revenue = CALCULATE([Actual Revenue], DATEADD('Calendar'[Date].[Date], -1, MONTH))

This is working fine.

Now i have a requirement to show the data of the last month as the default when the report is opened. In Power Query, i created a column called 'Last Month' that will have a value 'Last Month' for the last month, else null.

Expression = if Date.IsInPreviousMonth([Period]) then "Last Month" else "")

I use this column as a filter now. 

When i select June - 2022 from the slicer, the last month revenue displays the May - 2022's revenue correctly. But when i select the 'Last Month' slicer, the last month value is  same as that of the June month value. 

What am i missing? Please assist. 

Yours Truly,

Manikantan S

[Manikantans2]

Hi Amit, 

The solution you provided is working. I guess i had to "Mark as Date Table" for the calendar table in addition. Thanks for the solution. 

[amitchandak · Accepted solution]

@Manikantans2 , Create a column like this and try

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" , //Last Month
eomonth([Date],0)= eomonth(Today(),0),"This Month" , //This Month
[Month Year]
)

[Manikantans2]

Thanks Amit. The issue remains the same still.

Slight change to what you mentioned. 

Created a column as :

Month Type = SWITCH(TRUE(),

ENDOFMONTH('Calendar'[Date].[Date]) = EOMONTH(TODAY(),-1), "Last Month",

"")

Now i use this Month Type as a filter. On selection last month, the last month revenue (i.e. for May ) shows the same as June.