Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Thank you. It worked
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |