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.
Please could I ask for help. I think that I have a simple error in my formula but can not see what it is. I hope that I have not missed a previous post - I maybe describing it incorrectly.
Still very green but here we go:
I am using Power Pivot and with the following formula I get the desired results as shown below but have to filter the latest date out as they are not required in the pivot table. I have filtered out the date 06/07/20. Next week I need to filter out 14/07/20 and so on. I have Date in Axis and Dax in values.
All Campaigns:=COUNTROWS(VACampaigns)
I then want to go one step further and remove the latest date with the DAX so I do not have to change the filter every time. This is the formula that I wrote:
Filtered Campaigns:=COUNTROWS(FILTER(VACampaigns,VACampaigns[Latest Date] <> max(VACampaigns[Latest Date])))
But the pivot table changes and I end up with a grand total and the granularity of the date has gone. I do not know why this is happening or what I am overlooking:
What I have noticed is the following DAX presents a pivot table the way I expect:
CountByDate:=CALCULATE(COUNTROWS(VACampaigns),FILTER(VACampaigns,VACampaigns[Latest Date] =MAX(VACampaigns[Latest Date])))
but the moment I change the =MAX to <>MAX I go back to the single grand total coloumn.
What am I doing wrong/what is happening, my skill level is pretty basic.
Thank you for you time and help.
Coxy
Solved! Go to Solution.
Because when you are at 21/04 that is the MAX date when you are at 27/04 that is the max date the MAX function is evaluated in the filter context outside of you measure you can create a new calculated column to check if dates are less than a specific date and that column will return TRUE/FALSE and then use it inside CALCULATE
CALCULATE(
[Total Sales],
FILTER(
ALL( Dates ),
Dates[Date] <= MAX( Dates[Date] )
),
Dates[DatesLessThanMyDate] = TRUE()
)
Because when you are at 21/04 that is the MAX date when you are at 27/04 that is the max date the MAX function is evaluated in the filter context outside of you measure you can create a new calculated column to check if dates are less than a specific date and that column will return TRUE/FALSE and then use it inside CALCULATE
CALCULATE(
[Total Sales],
FILTER(
ALL( Dates ),
Dates[Date] <= MAX( Dates[Date] )
),
Dates[DatesLessThanMyDate] = TRUE()
)
Thank you @AntrikshSharma . Although I do not fully understand the formula, creating the calculated column to yeild a true/false column and using that does the job. I will study your solution further so I can fully appreciate your responce. Thank you.
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |