Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm looking to create a slicer for my dashboard that will allow me to switch from current period data (each year with it's total/year end data) to a YTD selection of the same data. The total crime chart below is an example of current year end period data. It's using a count from one table and the year coming from an "occured/entered date" collumn within that same table.
I am not familiar with how to set up a YTD measure using my date ranges and I noticed on other forums there was mention of creating a dates/calendar table, but I was unable to find an example of what that means and what data should be included so I'm hoping to get some examples to work from scratch with.
The closest example of what I'm hoping to set up is similar to this (apologiez for the low quality):
(source: https://www.havensconsulting.net/blog-and-media/creating-a-mtd/qtd/ytd-time-intelligence-slicer)
My options, however would be YTD and Overall/Current Period. The YTD data would be referring to the folder/databases "modified last" date which I have in a separate table.
Solved! Go to Solution.
Hi @Anonymous ,
To my understanding, you want to create a slicer with "YTD / QTD / MTD" to dynamically get the value .
Can you please share more details about your table and your expected output to help us clarify your scenario?
Below is a simply example:
Measure =
SWITCH (
MAX ( 'For Slicer'[Value] ),
"YTD", CALCULATE ( SUM ( 'Table'[Value] ), DATESYTD ( 'Table'[Date] ) ),
"QTD", CALCULATE ( SUM ( 'Table'[Value] ), DATESQTD ( 'Table'[Date] ) ),
"MTD", CALCULATE ( SUM ( 'Table'[Value] ), DATESMTD ( 'Table'[Date] ) )
)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
To my understanding, you want to create a slicer with "YTD / QTD / MTD" to dynamically get the value .
Can you please share more details about your table and your expected output to help us clarify your scenario?
Below is a simply example:
Measure =
SWITCH (
MAX ( 'For Slicer'[Value] ),
"YTD", CALCULATE ( SUM ( 'Table'[Value] ), DATESYTD ( 'Table'[Date] ) ),
"QTD", CALCULATE ( SUM ( 'Table'[Value] ), DATESQTD ( 'Table'[Date] ) ),
"MTD", CALCULATE ( SUM ( 'Table'[Value] ), DATESMTD ( 'Table'[Date] ) )
)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
How is "Current Period" different from YTD?
And yes, the recommendation is to use a calendar table that is fully covering your fact date range and any potential results from time intelligence functions like TOTALYTD, TOTALMTD etc.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |