topic Running Total Not Working in DAX Commands and Tips

Running Total Not Working

IC95 — Tue, 10 Dec 2024 16:08:59 GMT

Hi,

I hope someone can help me.

I have the Total Hours Efficiency Workcentre ( data I have)

I then have a measure with the Average Demonstrated Capacity(Last 3 Months)

Average Demonstrated Capacity (Last 3 Months) =

VAR LastMonth = EOMONTH(TODAY(), -1) -- Get the end of last month

VAR ThreeMonthsBack = EOMONTH(LastMonth, -3) + 1 -- Get the start of three months back

VAR FilteredDates =

DATESBETWEEN(

DIM_Date[theDate],

ThreeMonthsBack,

LastMonth

) -- Filter dates from three months back to the end of last month

RETURN

AVERAGEX(

FilteredDates,

[Demonstrated Capacity] -- Replace with your measure or column

)

The Past Dues is another measure which is basically the Total Hours Efficiency - Average Demonstrated Capacity(Last 3 Months)

Past Dues =

VAR PreviousOutput =

[Average Demonstrated Capacity (Last 3 Months)]

VAR CurrentWorkload = [Total Hours Efficiency Workcentre]

RETURN

IF( [Total Hours Efficiency Workcentre] = BLANK(), 0, CurrentWorkload - PreviousOutput)

I have tried doing the cumulative of the past dues to give me the result I want, but it is not working.

Past Dues running total in theDate =

CALCULATE(

[Past Dues],

FILTER(

ALLSELECTED('DIM_Date'[theDate]),

ISONORAFTER('DIM_Date'[theDate], MAX('DIM_Date'[theDate]), DESC)

)

IColumn G is the result I need, using an equation like the Column H but in PowerBi

Thank you in advance.

Re: Running Total Not Working

Greg_Deckler — Tue, 10 Dec 2024 17:36:13 GMT

IC95 Try: https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Better-Running-Total/m-p/2755666#M885

Re: Running Total Not Working

Anonymous — Wed, 18 Dec 2024 02:15:14 GMT

Hi IC95 ,

You can try the following expression.

Carry Over Past Dues = CALCULATE( SUMX( FILTER( ALLSELECTED('DIM_Date'), 'DIM_Date'[theDate] <= MAX('DIM_Date'[theDate]) ), [Past Dues] ) )

If this doesn't help. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

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

Re: Running Total Not Working

vivek31 — Wed, 18 Dec 2024 05:24:00 GMT

HI IC95 ,

you can try this measure to create running total

running total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL('Calendar'), 'Calendar'[DateKey] <= MAX('Calendar'[DateKey])))

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