Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
karolinaa
Frequent Visitor

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

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
        )
    )
1 ACCEPTED SOLUTION
karolinaa
Frequent Visitor

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!

View solution in original post

4 REPLIES 4
karolinaa
Frequent Visitor

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
Frequent Visitor

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?

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
Super User
Super User

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.