The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
All -
I am trying to show the most recent twelve date results for each selection "Year", "Quarter and Year", and "Month and Year" which are field parameters from my date table 'date'.
Date Table is the following:
Do I need to write a new measure for Volume that only pulls top 12 most recent date periods? In the Quarter and Year instance I only want to show Q1-Q4 for 2020 - 2022 volume. How do I write this?
Hi
Yes, you can create a measure that checks which data format is in scope and then filter the 12 most recent dates.
Something like this:
Volume Last 12 Periods =
RETURN
SWITCH (
TRUE (),
ISINSCOPE ( Date[Year] ),
CALCULATE(
[Volume],
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
YEAR
)
)
),
ISINSCOPE ( Date[Quarter] ) && ISINSCOPE ( Date[Year] ),
CALCULATE(
[Volume],
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
QUARTER
)
),
ISINSCOPE ( Date[Month] ) && ISINSCOPE ( Date[Year] ),
CALCULATE(
[Volume],
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
MONTH
)
)
)
Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
This did not work, it did not get rid of dates prior to the most recent 12 time periods and sum of the volume was inaccurate.
Would you mind sharing a pbix file with some sample data?
I want bottom right graph to look like other three when you switch between dates. (top 12 for each)
All I have for my fact table columns is uniuqe ID, volume amount, volume date.
I show DAX for data table, volume, and field parameters below.
My date table :
Date Table is the following:
I am not certain about how to share, there is no choose file option on the reply.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
126 | |
107 | |
87 | |
65 | |
60 |
User | Count |
---|---|
257 | |
121 | |
117 | |
100 | |
79 |