Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a matrix below, including customers and their KPIs, I need to keep the latest quarter sales by disabling the user's external filters like Year and Month. In other words, it is desired that the user's filter works on "TotalSales (PCS)" & "YOY%Change" and don't work on others.
I removed external filters on "Current Q" and "last 30 days" but it is almost impossible to remove that effect on the "Last Quarter."
I have tried the ALL() function, but it seems it doesn't work on the "PREVIOUSQUARTER()" function.
filters are as below,
all formulas are as below:
Solved! Go to Solution.
A different approach is to add the calculated column below to your date table:
Relative Quarter =
VAR vToday =
TODAY ()
VAR vResult =
DATEDIFF ( vToday, dDate[Date], QUARTER )
RETURN
vResult
Then rewrite your measure as follows:
last Quarter =
CALCULATE ( [TotalSales(PCS)], dDate[Relative Quarter] = -1 )
Proud to be a Super User!
A different approach is to add the calculated column below to your date table:
Relative Quarter =
VAR vToday =
TODAY ()
VAR vResult =
DATEDIFF ( vToday, dDate[Date], QUARTER )
RETURN
vResult
Then rewrite your measure as follows:
last Quarter =
CALCULATE ( [TotalSales(PCS)], dDate[Relative Quarter] = -1 )
Proud to be a Super User!
Thank you very much!
It seems that there is no way to remove the effect of external filters on the Time Intelligence built-in functions like "previous quarter" without creating a calculated column.
To remove the effect of external filters (year and month), I added the All() function to your formula:
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |