Forum Discussion

AlbLS's avatar
AlbLS
Frequent Visitor
7 years ago
Solved

Running Total per Month from a Measure

Hello everyone, I am new to the Power BI community. Thank you in advance for your help. I have a problem with a cummulative calculation from a measure. This calculation works perfectly for the year...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi AlbLS 

    I've simplified this for you, hope its what you need.

    # Entries = COUNTROWS( Entries ) 
    # Departures = COUNTROWS( Departures )
    # Difference = [# Entries] - [# Departures]
    # Difference Running Total = 
IF( 
    NOT ISEMPTY( Departures ) || NOT ISEMPTY( Entries ),
    CALCULATE(
        [# Difference],
        FILTER(
            ALL( 'Date' ),
            'Date'[Date] <= MAX( 'Date'[Date] )
        )
    )
)
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi AlbLS 

I've simplified this for you, hope its what you need.

# Entries = COUNTROWS( Entries ) 
# Departures = COUNTROWS( Departures )
# Difference = [# Entries] - [# Departures]
# Difference Running Total = 
IF( 
    NOT ISEMPTY( Departures ) || NOT ISEMPTY( Entries ),
    CALCULATE(
        [# Difference],
        FILTER(
            ALL( 'Date' ),
            'Date'[Date] <= MAX( 'Date'[Date] )
        )
    )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

  • AlbLS's avatar
    AlbLS
    Frequent Visitor
    7 years ago

    Hi Mariusz,


    Thank you very much for your answer.

    Unfortunately this does not solve my problem, as you can see in the following picture:

    What I need is a graph like this:

    when I'm going to filter for the year 2017, i will get this:

    and filter for the year 2018, this:

     

    I hope we can find the solution, for the moment I can not do it.

     

    Thank you very much again.

     

    Alberto

      • AlbLS's avatar
        AlbLS
        Frequent Visitor
        7 years ago

        Thank you very much Mariuzs.

        Your solution works perfectly, I really thank you. In addition, you have simplified the task because now I do not need different formulas for years and months, with your solution it works for both cases. It's perfect!

        There is only one thing that I changed, I deleted from your formula the following part:
        IF (
             NOT ISEMPTY (Departures) || NOT ISEMPTY (Entries)
        )
        Because it caused empty columns when there were no entries and no departures for a year or for a month. As we see here:

        But i delete the IF statement, it works very well.

        Thanks again.

        Alberto