Relative Calculate Filter Value

[benjamin_sasin]

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

[amitchandak]

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-functions

Appreciate 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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[benjamin_sasin]

Thank 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.

[v-eachen-msft]

Hi @benjamin_sasin ,

"FILTER ([Category] = MAX ([Category])))" is intended to be grouped by category, similar to the ALLEXCEPT () function.

Your DAX can be edited as the following measure:

Cumulative per Academic Year =
CALCULATE (
    COUNT ( 'Sheet1'[user_id] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[academic_year] ),
        Sheet1[date] <= MAX ( Sheet1[date] )
    )
)

[benjamin_sasin]

Thank you @v-eachen-msft ,

The problem I have with that is that I cannot then filter down the count by other subcategories (slicer) and have a meaningful visual. For example, I have another column "program" and in case I select a program in a slicer, the visual won't filter. That's why I used ALLSELECTED() then.

So to clarify, inside FILTER(), the ALLEXCEPT(Sheet1, Sheet1[academic_year]) function basically says "group the calculation by academic year", correct?

Why then does FILTER ([Category] = MAX ([Category])) does the same? IT is really unintuitive. What then would be the actual syntax to filter against the max, as in "count user_id only if academic_year is the maximum academic_year"?