Forum Discussion

Gaurav_84's avatar
Gaurav_84
Helper I
1 year ago
Solved

Cumulative Value fix

Hi Expert,

 

Need you expert advice in fixing my dex query for calculting cumulative value for each year seprately. For first 11 period in 2023 the values are getting calcuated currectly. But from period 12 it started to show some different value till the end of 2024.

 

Based on the formula (CALCULATE(SUM('TEST data'[amount]),FILTER(ALLSELECTED(DimDate[Date]),DimDate[Date]<=MAX(DimDate[Date])))

I am getting below values. till period 11, 2023 its all good.  I am looking to claculate values in expected column.

 

For year 2024 i am expecting No values show be carry forward from 2023. calculation will start for 2024. example Period 1 year 2024 ( amount 2007705.14 + (-1468836.83) = 538,868.31) Period 2 ( 538868.31+(-251787.23) = 287081.08.

so on.

 

Regards

Gaurav

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Gaurav_84 ,

     

    You can try to create a measure.

    MEASURE = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Fiscal Year] = MAX ( 'Table'[Fiscal Year] )
            && 'Table'[Period] <= MAX ( 'Table'[Period] )
    )
)

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

5 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    1 year ago

    Hi Gaurav_84 

     

    You can use the built in Time Intelligence in DAX. You will need a date table (See my Bio)

     

    Create a base measure

     

    Total Amount = SUM('TEST data'[amount])

     

     

    Then a YTD

     

    Total Amount YTD =
TOTALYTD([Total Amount], 'Date'[Date])

     

     

    Add the columns from the Date table to your table visual and then the YTD measure and this will calculate correctly

     

    Hope this helps

    Joe

    • Gaurav_84's avatar
      Gaurav_84
      Helper I
      1 year ago

      Thanks Joe,

       

      Unfortunately its still not work i have created one dim date table & one date column in my Test data. It calculate the value only from Period 1 Ignoring previous total value. 

      For example for period 02 2024 it calculate 1,720,624.06 (1468836.83+251787.23) but it ignore value 2007705.14)

       

      Date column in my test date using following formula (DATE('TEST data'[Fiscal Year],'TEST data'[Period ],1))

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Gaurav_84 ,

     

    You can try to create a measure.

    MEASURE = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Fiscal Year] = MAX ( 'Table'[Fiscal Year] )
            && 'Table'[Period] <= MAX ( 'Table'[Period] )
    )
)

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

    • Gaurav_84's avatar
      Gaurav_84
      Helper I
      1 year ago

      Thanks Clara, It work 🙂

      Can you please also suggest what modification needs to be done in your formula to calculate previous month cumulative total.