Forum Discussion

Fabi's avatar
Fabi
Frequent Visitor
3 years ago
Solved

Cumul with filter date

Hi all !

 

I have a report like this :

I would like that, when I select a year in the filter, it sums the selected year and all the previous years.

Exemple : if I select 2021 in the filter, I should therefore have A = 4, B = 11 and C = 14. If I select 2022: A = 6, B = 15, C = 20.

 

I tried with several calculations (CALCULATE, SELECTEDVALUES, ALL, etc.) but I didn't succeed, using the dates filter always breaks everything.

 

If anyone has an idea that would be great because I'm really stuck... Thank you 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Expected measure: =
CALCULATE ( SUM ( Data[Value] ), 'Year'[Year] <= MAX ( 'Year'[Year] ) )

     

3 Replies

  • Fabi's avatar
    Fabi
    Frequent Visitor
    3 years ago

    Thanks FreemanZ  and Jihwan_Kim ,

     

    The solution of Jihwan_Kim work simply.

     

    In my real data, I was working with a date field and it didn't work. I had to extract in another column the year and use this one for it to work.

     

    Thanks !

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Expected measure: =
CALCULATE ( SUM ( Data[Value] ), 'Year'[Year] <= MAX ( 'Year'[Year] ) )

     

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago
    Supposing your table named Data, try to plot the measure with the code below:
    Measure := 
    VAR _year =
        SELECTEDVALUE ( Data[Year])
    VAR _Result =
    CALCULATE (
        SUM(Data[inscriptions]),
        Data[Year]<=_year
    )