Forum Discussion
Resolver IRelative Calculate Filter Value
I'm trying to figure out how to segement a calculated cumulative measure by breaking it down by a value in a third field (in my case "academic_year").
This has to do with the use of FILTER() with the value relative to the row calculated, such as FILTER(table[field] = [field]) rather than FILTER(table[field] = "absolute value").
With the following formula, I am obviously cumulating the previous academic year with the following one:
Cumulative = CALCULATE(COUNT('Sheet1'[user_id]),FILTER(ALLSELECTED(Sheet1),Sheet1[date] <= Max(Sheet1[date])))
I thought I could do the following to solve the problem of cumulating only within the same academic year:
Cumulative per Academic Year = CALCULATE(COUNT('Sheet1'[user_id]),FILTER(ALLSELECTED(Sheet1),Sheet1[date] <= Max(Sheet1[date]) && Sheet1[academic_year] = [academic_year]))
But that didn't change anything.
I also tried with COUNTROW(Sheet1) instead but that didn't work either, so I must be using the filter wrong.
(Also where is the clear documentation about this? I can't find anything relevant online. So links would be appreciated.)
PS I don't see any button to upload files here: so here is a dropbox link: https://www.dropbox.com/s/xd4fk8p4k9tifde/test_cumulative.pbix?dl=0
5 Replies
- amitchandak
Super User
If you want to reset at the start of the year then use datesytd or totalytd. In both functions, you can give end date of year.
YTD Sales = CALCULATE(COUNT('Sheet1'[user_id])),DATESYTD(('Date'[Date Filer]),3/31""))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functionsAppreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
benjamin_sasinThank you, however it's not quite what I need. Although it's close to be a YTD, it's note quite one either, since:
1. The date of admission (closed deal) are not matching the annual calendar but follow an academic calendar (April-March).
2. The date of application (start of deal) can trail more than a year before the closure of the deal.
Therefore, in the data set of the example file I provided, there's a separate colum refrerring to the academic year.
What would be the right syntax for FILTER(admissions[academic_year] = [academic_year])?
- benjamin_sasin
So it seems I have found the solution eslewhere, but I am completely baffled by it.
Apparently the correct syntax is:
Cumulative per Academic Year = CALCULATE(COUNT(Sheet1[user_id]),FILTER(ALLSELECTED(Sheet1),Sheet1[date] <= Max(Sheet1[date]) && [academic_year] = max(Sheet1[academic_year])))Now it's a complete mystery to me as to why I should be using MAX() in FILTER([category] = MAX([category])). What has the max to do with it? If I use the max here, then how would I do an actual formula where I would want my calcultation to be filtered only where the category matches the actual max value within that category?
As much as the Power M Query Language makes sense to me (and for reference, I can use Python and Javascript and SQL), I just can't wrap my mind around DAX.
Can anyone explain please? Thank you.
