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

     

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