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

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

 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share the download link of the PBI file.