Filter a visual by the same month in the last 3 years

[karolinaa]

Hi all, I have a measure (no. of units sold per month), date table (fiscal year) and a clustered column chart.

Retail Sales Monthly = CALCULATE(DISTINCTCOUNT(SalesTable[salesID]),TREATAS(VALUES(DateTable[cal_date]),SalesTable[sold_date]))

Date table contains the calendar date, month number, month name, year and a month difference (e.g. previous month is -1, previous month a year later is -13, etc.).

To show only the same month in the last three years (e.g. August'23, August'22, August'21), I used to do it using the basic visual filter (month difference = -1 or -13 or -25). 

Now, I added the date slicer where the user puts the max. calendar date (e.g. 01.05.2023).

I need the visual to show the last completed month and the same month last year and two years ago (April'23, April'22, April'21).

I was trying to apply the Top N visual filter, but I cannot find the way of maximizing the e.g. April'22 value above June'23 ("show Top 3 values"). 

Is there any way of adjusting the measure to show only those 3 months? Or is there a way to do in the Top N filter? I was trying to add the filter into the measure (e.g. here only to show the last month), but it wasn't successful. 

Test =

VAR last_month = MAX(DateTable[MO_DIFF])

RETURN IF(

    last_month > -1,

    BLANK(),

    CALCULATE(

        [Retail Sales Monthly],

        DateTable[MO_DIFF] = last_month

        )

    )

[karolinaa · Accepted solution]

I managed to solve the issue.

The problem was that my MAX(Date) measure did not work as a variable because it had no proper connection to the slicer. The selectedvalue() did not work either due to the "date between" slicer. 

This is the solution, it works perfectly: 

Retail Sales Monthly =

VAR max_selected_mo_diff = MAXX(ALLSELECTED(DateTable), DateTable[MO_DIFF])

VAR max_selected_mo_diff_LY = max_selected_mo_diff - 12

VAR max_selected_mo_diff_LY2 = max_selected_mo_diff - 24

VAR setx = {max_selected_mo_diff, max_selected_mo_diff_LY, max_selected_mo_diff_LY2}

RETURN CALCULATE(

    [Retail Sales Monthly],

    FILTER(DateTable, DateTable[MO_DIFF] IN setx))

Thank you for your time!

[karolinaa]

Hi Ibendlin, thanks for your answer!

Unfortunately, whichever logic I apply (whether it is the month number or month diff above), the measures don't work:

Test_Month =

VAR _MaxDate = MAX(DateTable[cal_date])

VAR MonthNum = CALCULATE(

    FIRSTNONBLANK(DateTable[MoNum], TRUE()),

    FILTER(ALL (DateTable), DateTable[cal_date] = _MaxDate))

RETURN CALCULATE ([Retail Sales Monthly], DateTable[MoNum] == MonthNum)

Now, if I replace MonthNum in the RETURN with any number, it works perfectly.

The variable itself works too, it's just that it doesn't work in the filter in Return. Any ideas why?

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

In your measure get the max calendar date (based on the slicer selection ) and then filter your fact table by MONTH([fact date])=MONTH(maxcaldate)  etc.