Forum Discussion

MattyH's avatar
MattyH
Frequent Visitor
5 years ago

Calculated Columns - Cumulative Totals problems

Hey Guys

 

Im trying to create YTD totals for daily hours (for people) in a calculated column using DAX. I can do it in M (see running total column) but want to do it in DAX.  I have an IF function added in to create and A or B depending on when i go over 5000 hours.

 

 

1. This DAX gave sort of gave me  YTD but  it doesnt really seem to work on a daily basis when i have multiple ROW hours on the same day  and just gives me the same YTD value. I want a row level YTD.

YTDHoursCC =

CALCULATE (

SUMX (Financials, 'Financials'[Hours] ),

ALL ( 'Financials' ),

DATESYTD ( 'Financials'[Date] )

)

 

2. I then created this DAX  after i added an index but it only gave me the daily hours withour adding them  up.

YTDTotalCC2 =
CALCULATE (

SUM ( 'Financials'[Hours] ),

FILTER (

ALL ( Financials[Index]),

'Financials'[Index]

<= MAX( 'Financials'[Index])

)

)

 

Can someone educate me please?

 

 

 

 

1 Reply

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    5 years ago

    Hi MattyH ,

    Try using these measures for creating Running total:
    1. Running total= CALCULATE(SUM(Financial[Hours]), ALLSELECTED(Dates)), Dates< = MAX( Date)))

    Or

    2. Running total= CALCULATE( SUM( Financial[hours]), FILTER( ALLSELECTED(Dates)), ISONORAFTER( Dates, MAX( Dates)), desc)))

     

     

    I hope this helps!