Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Running Total measure

Hi guys,

I'm trying to do a "simple" running total measure and it's not working....

1. I've create 2 measures that sum costs and another for revenues
TGross Costs = SUM(ICM[Gross Costs])
TGross Rev = SUM(ICM[Gross Revenues])
2. I've create another that shows the difference between these 2
Margin Diff = [TGross Rev]-[TGross Costs]
3. And then i've created the running total measure
Margin Acc =
CALCULATE(
[Margin Diff],
FILTER(
ALLSELECTED('ICM'[Date]),
ISONORAFTER('ICM'[Date], MAX('ICM'[Date]), DESC)
)
)

It's not working. Can anyone help me...please?

Thanks in advance

Best regards

Pedro

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Try the following and see if this works for you:

     

    CALCULATE (
    [Margin Diff],
    FILTER (
        ALL ( 'ICM'[Date] ),
        'ICM'[Date] <= MAX ( 'ICM'[Date] )
    )
)

     Kind regards
    Joren Venema

    Data & Analytics Consultant
    If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey Joren, 

      It doesn't work and it shows some "weird" values....
      Let me tell you what i've discover just now: if i let in the report just this Date, Gross Rev, Gross Cost and Margin acc, it works. But if i add another field it won't.
      Could you explain me this? Can i add another field, and maybe change something in that running total measure?
      Thank you so much for all your help

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        The weird values are caused by the fact that we used the ALL() Function. This ignores all the filters and returns an unfiltered table. When you add a column that is not date the the newly added column(filter in this case) gets ignored which causes the "Weird" outcome. This all has to do with Power BI's filter context.

         

        If you have a dedicated calendar table you can use this to filter the outcome of the expression and still have other filters in your report.

         

        Try and see if this works for you:

        Measure = CALCULATE (
    [Margin Diff];
    FILTER ( ALLSELECTED(MasterCalendar);
        MasterCalendar[Date] <= MAX ( MasterCalendar[Date] ))
)
)

        Kind regards
        Joren Venema

        Data & Analytics Consultant
        If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.