Forum Discussion
Help with IF statement for cumulative totals
Hi I would like to have a Cumulative Total calculate by Company. My issue is that it is giving the cumulative total for the whole period and not differentiating by the Company type. ...
I found a solution!!
Cumlt Hrs to Date Company = CALCULATE(SUM(ACCOUNT_HISTORY[Hours To Date]), ACCOUNT_HISTORY[PERIOD]<=EARLIER(ACCOUNT_HISTORY[PERIOD]),ALLEXCEPT(ACCOUNT_HISTORY, ACCOUNT_HISTORY [ACCOUNT.COMPANY]))
It works :)
Anonymous,
To be general, you modify your measure using dax below and check if it can meet your requirement:
Hrs To Date =
CALCULATE (
SUM ( ACCOUNT_HISTORY[Hours To Date] ),
FILTER (
ACCOUNT_HISTORY,
'ACCOUNT_HISTORY'[ACCOUNT.COMPANY] = 'ACCOUNT_HISTORY'[ACCOUNT.COMPANY]
&& 'ACCOUNT_HISTORY'[PERIOD] <= EARLIER ( 'ACCOUNT_HISTORY'[PERIOD] )
)
)
Cost To Date =
CALCULATE (
SUM ( ACCOUNT_HISTORY[Cost To Date] ),
FILTER (
ACCOUNT_HISTORY,
'ACCOUNT_HISTORY'[ACCOUNT.COMPANY] = 'ACCOUNT_HISTORY'[ACCOUNT.COMPANY]
&& 'ACCOUNT_HISTORY'[PERIOD] <= EARLIER ( 'ACCOUNT_HISTORY'[PERIOD] )
)
)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I found a solution!!
Cumlt Hrs to Date Company = CALCULATE(SUM(ACCOUNT_HISTORY[Hours To Date]), ACCOUNT_HISTORY[PERIOD]<=EARLIER(ACCOUNT_HISTORY[PERIOD]),ALLEXCEPT(ACCOUNT_HISTORY, ACCOUNT_HISTORY [ACCOUNT.COMPANY]))
It works :)