Forum Discussion

AlanP514's avatar
AlanP514
Post Patron
4 years ago
Solved

Dynamic Cumulative Value

Hai All Need your help on this

I want to create Dynamic Cumulative totals Meaning  from 2015 to the Current year I want to add  Target value 
For eg CY Is 2022 so i want to add cumulative from 2015 to 2022 
If CY Is next year means i want to add up to 2023 ,
 So Target of 2015 to CY cumulative values please help me on this dax

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi AlanP514 ，

    Please refer to  amitchandak 's formula and modify it.

    Create a measure.

    Measure =
CALCULATE (
    SUM ( 'table'[sales] ),
    FILTER (
        ALL ( 'table' ),
        YEAR ( MAX ( 'table'[date] ) ) >= 2015
            && YEAR ( MAX ( 'table'[date] ) ) <= YEAR ( TODAY () )
    )
)

    Or a column.

    column =
CALCULATE (
    SUM ( 'table'[sales] ),
    FILTER (
        'table',
        YEAR ( 'table'[date] ) >= 2015
            && YEAR ( 'table'[date] ) <= YEAR ( TODAY () )
    )
)

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

     

    Best Regards
    Community Support Team _ Polly

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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    AlanP514 , If you have date

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])

    && year(date[date] ) <= year(today())  && year(date[date] ) >= 2015 ))

     

    if you have a year , if you do not have a year table, use your table(prefer a separate year table)

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(year ),year[year ] <=max(year [year ])

    && year [year ]  <= year(today())  && year [year ]>= 2015 ))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AlanP514 ，

    Please refer to  amitchandak 's formula and modify it.

    Create a measure.

    Measure =
CALCULATE (
    SUM ( 'table'[sales] ),
    FILTER (
        ALL ( 'table' ),
        YEAR ( MAX ( 'table'[date] ) ) >= 2015
            && YEAR ( MAX ( 'table'[date] ) ) <= YEAR ( TODAY () )
    )
)

    Or a column.

    column =
CALCULATE (
    SUM ( 'table'[sales] ),
    FILTER (
        'table',
        YEAR ( 'table'[date] ) >= 2015
            && YEAR ( 'table'[date] ) <= YEAR ( TODAY () )
    )
)

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

     

    Best Regards
    Community Support Team _ Polly

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