Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
HI Guys,
I have a quick question:
- Lets say I filtered my entire report on Calendar Date is in "Previous Week"
- But I need one of my measures to check if something was assorted in the last 6 months
I used the formula:
Solved! Go to Solution.
@powpowpow
The page filter only changes the displayed visual, the calculating result still take the last 6 months value.
For example, I have a measure calculate the last month value, when I set page filter to before 2020/2/1. The result 193 doesn't change, only the visual is filtered.
Best, regards
Paul Zheng
@powpowpow
The page filter only changes the displayed visual, the calculating result still take the last 6 months value.
For example, I have a measure calculate the last month value, when I set page filter to before 2020/2/1. The result 193 doesn't change, only the visual is filtered.
Best, regards
Paul Zheng
Hi Paul,
That's what I thought thanks a lot for confirming ! 😄
The weird thing is that for another measure of the same type, I was trying to sum all shipments that were made in the last three weeks. Knowing that weeknum([Last Saturday]) is 8 and that the visual is filtered on week 8 data only (Variable Fiscal Week is set to Previous Week), I assumed that the below formula would give me the result the sum of shipments made on week 6, 7 and 8. Unfortunately, it only sums the shipments made on week 8, thus limited by the report level filter. 😞
Transit = CALCULATE(SUM(Shipments[Gross Units]),
ALL('Calendar'[Variable Fiscal Week]),
filter(all('Calendar'),'Calendar'[Calendar Week] > (weeknum([LastSaturday],1) - 3) && 'Calendar'[Fiscal Week Num] <= (WEEKNUM([LastSaturday],1)))
Data: I have
Calendar week = 6 -> Shipments = 6 units
Calendar week = 7 -> Shipments = 3 units
Calendar week = 8 -> Shipments = 12 units
But Transit measure results in 12 (shipments on week 8, Weeknum of last saturday)
Instead of summing of the three and be 21 units
Any idea on why it is doing this and how to resolve ? would greatly appreciate
Seems fine or you can try like example below
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-6,MONTH))
I would expect that the ALL should get rid of the page level filter if it is filtering the Calendar table.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |