How to get cumulative value only by one column

[Anonymous]

Hi, I want to create a YTD and there is some trouble when use TOTALYTD and DATESYTD, so I want to use calculate instead, 

here is the result someone told me, but there is a problem that when added another column, the YTD does not goes well, I want the YTD just cumulatived by date column whatever other column added,  is there a measure or column can solve it?

here is the calculation 

VAR _current_date =

    MAX ('Query1'[date])

VAR _current_year =

    YEAR ( _current_date )

RETURN

    CALCULATE (

        SUM('Query1'[value]),

        YEAR ( 'Query1'[date] ) = _current_year,

        'Query1'[date]<= _current_date

    )

and when added REMOVEFILTERS('Query1'[week]) , REMOVEFILTERS('Query1') or ALLEXCEPT('Query1','Query1'[date]) in calculation still did not get expected result

there is no problem with the YTD when no column add

after add the week column, YTD fails to goes well

[Ashish_Mathur]

Hi,

Create a Calendar table.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Date from the Calendar Table.  Write these measures

Total = SUM('Query1'[value])

Total YTD = calculate([Total],datesytd(calendar[Date]))

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous 

I made a change on your code, you can try it:

measure = VAR _current_date =
    MAX ('Query1'[date])
VAR _current_year =
    YEAR ( _current_date )
RETURN
    CALCULATE (
        SUM('Query1'[value]),
       FILTER(ALLSELECTED(Query1),YEAR(Query1[date])=_current_year&&Query1[date]<=_current_date)
    )

Best Regards!

Yolo Zhu

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

[amitchandak]

@Anonymous , prefer to use a date table and new window function

explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

or use a measure like

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

or

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

Use date table in viusal and filter

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41