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.
Say I have created dates columns with YTD and MTD periods returning TRUE if in period and FALSE else, and want to use a these columns to filter my data with a slicer. The slicer is based on another table called Dimperiods. I came across one solution here, but the problem with this approach is that the Dates table needs to be represented in the visual for this to work, which is not ideal. Is there a way to work around this? Selecting YTD should filter all the data so inYTD = TRUE.
Hi @Veblengood ,
I created some data:
Here are the steps you can follow:
1. Create measure.
Flag =
var _select=SELECTEDVALUE('Dimperiods'[PeriodID])
return
SWITCH(
TRUE(),
_select="MTD"&&MAX('Date'[Date]) >= DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&MAX('Date'[Date])<=TODAY(),1,
_select="YTD"&&MAX('Date'[Date]) >= DATE(YEAR(TODAY()),1,1)&&MAX('Date'[Date])<=TODAY(),1,0)
Measure =
SUMX(ALLSELECTED('Date'),[Value])
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Whether this result meets your expectations
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
Thanks, but this only works if Date is included in the table. It does not work if you wish to use the measure to filter other visual where Date is not contained.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |