Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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:
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:
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:
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...
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
80 | |
64 | |
52 | |
48 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |