- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
5 years rolling data based on slicer (year) selection
Hi All,
I have a requirement that needs to display 5 years of data if selected YTD, 12 months of data if selected MTD, selected month && 4 quarters of data if selected QTD.
How do I get 5 years of data?
Please drop me your suggestions or answers.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Powerbyosh ,
Here are the steps you can follow:
1. Enter – Create a table.
2. Create measure.
Flag =
var _select=SELECTEDVALUE('Table2'[Group])
var _today=TODAY()
return
SWITCH(
TRUE(),
MAX('Table2'[Group])="YTD" && MAX('Table'[Date]) >= DATE(YEAR(_today)-5,MONTH(_today),DAY(_today))&& MAX('Table'[Date]) <= _today,1,
MAX('Table2'[Group])="MTD" && MAX('Table'[Date]) >= DATE(YEAR(_today)-1,MONTH(_today),DAY(_today))&& MAX('Table'[Date]) <= _today,1,
MAX('Table2'[Group])="QTD" && MAX('Table'[Date]) >= DATE(YEAR(_today),MONTH(_today)-4,DAY(_today))&& MAX('Table'[Date]) <= _today,1,0
)
Measure =
SUMX(
FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])),[rand])
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. It worked
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-05-2024 08:22 AM | |||
05-28-2024 09:28 AM | |||
08-22-2024 01:54 AM | |||
06-19-2024 05:33 AM | |||
07-16-2024 02:55 AM |
User | Count |
---|---|
21 | |
18 | |
17 | |
7 | |
5 |
User | Count |
---|---|
32 | |
27 | |
19 | |
13 | |
12 |