Forum Discussion

IC95's avatar
IC95
New Member
1 year ago

Running Total Not Working

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.

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • vivek31's avatar
    vivek31
    Resolver II
    1 year ago

    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.