Forum Discussion

benjamin_sasin's avatar
benjamin_sasin
Resolver I
6 years ago

Relative 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...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

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-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_sasin's avatar
    benjamin_sasin
    Resolver I
    6 years ago

    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's avatar
      benjamin_sasin
      Resolver I
      6 years ago

      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's avatar
        v-eachen-msft
        Community Support
        6 years ago

        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] )
    )
)