Forum Discussion

IC95's avatar
IC95
New Member
1 year ago

Running Total Measure

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.

 

Need Help

3 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    1 year ago

    You are in PowerQuery section...

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    1 year ago

    use this

    Past Dues Running Total =
    CALCULATE(
    [Past Dues],
    FILTER(
    ALL('DIM_Date'),
    'DIM_Date'[theDate] <= MAX('DIM_Date'[theDate])
    )
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi IC95 ,

     

    Please try this:

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

    Posting the question to a relevant forum can greatly increase the probability of getting it resolved.

    DAX Commands and Tips - Microsoft Fabric Community

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum