Dax to a cumulative sum

[brunoemr]

Hi All,

I'm struggling here...

I'm trying to do a chart with a cumulative sum of hours reported during the contracts period for each contract.

Reported Hours are in Timeworked table and the contract period are in TblContracts, fields TblContracts[Contract Start Date] and TblContracts[Contract End Date] don't have any relations.

My data model:

I'm using the Dax below, it returns the reported hours on the period, but it does not return the cumulative values.

Cumulative TimeWorked =
var starts_ = MAX(TblContracts[Contract Start Date])
var ends = MAX(TblContracts[Contract End Date])
return
CALCULATE(
SUM(Timeworked[Total_time_in_hours])
,FILTER( Date_dim,
Date_dim[full_date] >= starts_ &&
Date_dim[full_date] <= ends
)
,CROSSFILTER (Timeworked[date_dim_id],Date_dim[date_dim_id], BOTH )
,CROSSFILTER ( Timeworked[task_dim_id], Overview[project_task_dim_id], BOTH)
)

How can I achieve the cumulative values?

Thanks in advance.

BrunoR.

[brunoemr · Accepted solution]

Hi,

Have found a solution.

Final Dax:

Cumulative TimeWorked = 
VAR minDate =
    MIN ( TblContracts[Contract Start Date] )
VAR maxDate =
    MAX ( TblContracts[Contract End Date] )
VAR totalHours =
    CALCULATE (
        SUM ( Timeworked[Total_time_in_hours] ),
        FILTER (
                ALL ( Date_dim ),
                Date_dim[date] >= minDate
                && Date_dim[date] <= MAX ( Date_dim[date] )
                && ( MAX ( Date_dim[date] ) <= TODAY() && MAX ( Date_dim[date] ) <= maxDate)
        )
        ,   CROSSFILTER ( TblContracts[SNOW reporting ticket - billable ], Overview[project_task_number], BOTH )
        ,   CROSSFILTER ( Timeworked[task_dim_id], Overview[project_task_dim_id], Both)            
        ,   CROSSFILTER ( Timeworked[date_dim_id], Date_dim[date_dim_id], BOTH )
    )

return totalHours

Thanks,

Bruno

[amitchandak]

@brunoemr , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[brunoemr]

For sure, here it is a sample:

My Datedim have all dates from 2013 to 2030, I need to avoid future dates.

Thanks