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 years, but it does not work for months. Let me explain:

I have two tables of human resources (entries and exits of employees):
- Entries
- Departures

 

 

 

 

 

 

 

 

 

 

 

I need to know the evolution of the workforce of the company, by year but also by month (to know the monthly evoulution in each year)
I mean, I need to create a chart with the total cumulate number of employees (running total)

To do this, I created several things:
- A date table
- Measures:
SumAnual Entries (sum of entries per year)
SumAnalDepartures (sum of departures per year)
DiffEntriesDepartures_YEAR (difference between the two preceding measures, that is the workforce of the company at the end of each year)
RunningTotalWorkforce_YEAR (cumulative effect after year)

 

For the year, it works perfectly.

The same thing for months, it does not work at all.

You think the problem comes from where? I tried many things but nothing worked.

 

 

 

 

 

 

 

 

 

I leave you my power BI file.

https://drive.google.com/open?id=1iuSBrca3SS5JhMAYQGAowvKDamPjtl-d

I thank you so much if anyone can help me.

Best regards,

Alberto

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

     

9 Replies

  • 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

      Hi Ashish,

      Thank you very much for your answer and for your power bi file.

      Unfortunately it does not solve my problem either, I already had the same result as you, but when the year changes, running totoal starts from scratch, and this is a mistake.

      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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

        Change the New Joinees YTD measure to:

        Net joinees YTD = CALCULATE([Net joinees],DATESBETWEEN('Date'[Date],MINX(ALL('Date'),'Date'[Date]),MAX('Date'[Date])))

        Hope this helps.