cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Cumulative Recurring SUM per Sector

Hi all,

 

Looking for some help to calculate a cumulative recurring total per sector.
Basically, i have the following data (with around 50 different IDs, and 12 differents sectors).

ID

Sector

Savings per year

Start Date

End date

1

HR

                32,353.13  $

2018-10-15

2019-10-15

2

IT

                32,353.13  $

2018-12-15

2019-02-15

3

HR

              662,291.67  $

2018-12-15

2019-03-15

4

OP

                32,353.13  $

2018-12-15

2019-10-15

5

IT

              841,500.00  $

2018-09-15

2019-10-15

 

I want to calculate the monthly saving (therefore, divings the savings per year by 12) and showing the monthly cumulative savings by sector.
For example:
- For Jan 2019, I want to have a total of all savings / 12 (As january 2019 is between the start date and end date). It would be broken down the following way: (HR (662K + 32K) + IT (32K + 841K)+OP(32K))/12 = Total saved for january 2019.

However, when I tried calculating it a new measure, I get the following chart:Temp_PowerBI_Issue_2019-02-24.PNG
This is the formula I have used to calculate the cumulative gain:

Total savings per month =

CALCULATE(

    SUM(TableOperationnel[Savings per year]),

    FILTER(

        ALLSELECTED(TableOperationnel),

        TableOperationnel[Start Date] <= MAX(TableOperationnel[Start Date])

    )

)

 

 

Has anyone faced a similar situation before? Any help would be appreciated!

Thanks 🙂

Ben

2 ACCEPTED SOLUTIONS

Hi,

 

Do you want something like this.  You may download my file from here.  I am accumulating savings from the start of the Year (January 1), rather than the start of the period for which data is available.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post



When I tried to use Dates from the 'Dates' table, it generates an error.


What was the error? Was it related to the bi-drectional relationship between the Dates and the Operational table? I'm not sure why you'd do that. Normally I'd have a 1 to many relationship from Dates to a fact/transaction table.

 

Any idea how to make it the reccuring saving appear for every month that is between [Start Date] and [End Date]?

You can't do a between join using relationships in a tabular model. But you can achieve the same effect by not having a join between your Date and Operational tables and doing the "between join" logic in a measure.

 

2019-03 cummulative between.png

I used the following 2 measures to achieve the output above.

 

Amt = sumx(
        filter(sales, 
            Sales[StartDate] < Max('Date'[Date]) 
            && Sales[EndDate] > min('Date'[Date])
        )
    , Sales[Amount])
Cummulative Amt = CALCULATE(
    [Amt] 
    , FILTER(ALL('date'), 'Date'[Date] <= max('Date'[Date]))
)

 

You can download a copy of this model from here

https://1drv.ms/u/s!AnSKp2UbblSYgbUQdmNBM926iPxTlQ

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Thank you very much to both of you for your help!

@Ashish_Mathur's solution ended working for me. @d_gosbell The difference between both of your solutions was that Ashish's was cumulative per month. My graph now looks like Ashish's graph in his previous response.


Thanks again to both of you 🙂

Ben




@Ashish_Mathur's solution ended working for me. @d_gosbell The difference between both of your solutions was that Ashish's was cumulative per month. My graph now looks like Ashish's graph in his previous response.

 

I think the main difference in our approaches is that I've done a "Lifetime to Date" while Ashish has done a "Year to Date" (so it resets on the first month of the year). Both are valid depending on the requirements

Anonymous
Not applicable

Hi,

Just reviewed both of your solutions more in details. Both actually hepled as I also used @d_gosbell to not keep a YTD limit.
I have also ended up using his formulas as the SUMX seemed to represent more the actual recurring savings per month.
Ashish's solution helped me set one line per month in between start date and end date. 

Thanks and cheers,
Ben

That's great news @Anonymous thanks for letting us know. I think that shows the true power of forums like this - when you can pick up pointers from a couple of different responses and combine them into something that fits your requirements. 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

I do not understand.  ID 3 is for a duration for 3 months only.  Why should the number be divided by 12?  Savings per month shoul d be number/3.  Am i correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
d_gosbell
Super User
Super User

I think this issue is because you are using ALLSELECTED, so for the October column in your chart ALLSELECTED will only return October dates (as October is part of the current selection).

If you switch this to use the ALL(TableOperationnel[Start Date]) function it should fix your issue.

eg.

 

Total savings per month =

CALCULATE(

SUM(TableOperationnel[Savings per year]),

FILTER(

ALL(TableOperationnel[Start Date]),

TableOperationnel[Start Date] <= MAX(TableOperationnel[Start Date])

)

)

Anonymous
Not applicable

Hi d_gosbell,


Thanks a lot for your reponse! Your suggestion worked as it seems to show now in Power BI all the data I had in my data source (See screnshot below):

Temp_PowerBI_Issue_2019-02-25.PNG


However, the reccuring saving appears only once instead of appearing for every month. For example, the saving that appears in novembre (which therefore, has a start date somewhere in novembre), does not appear for the subsequent month. It should also be divided by 12 to illustrated the average monthly saving.

How could I go from the current chart to one that:

- Divides the saving by 12

- Appears at every month between start date and end

- is grouped by sector

 

Do I have to create a new table? Is there a specific function/formula that can process this logic?

Thanks!
Ben

So normally I split all of my descriptive columns that I want to do grouping and filtering on into separate tables. So I'd have a table for dates that might have Date, Month, Quarter, Year, etc and a table for the sectors. I think the problem here is that each month is only doing the cumulative sum for sectors that have values in that month. Having the sectors in a separate table that is related to your main data table would avoid this issue.

 

However we can possibly also fix this by using ALL() over the sector column and if you want to divide this by 12 you should be able to add this at the end.

 

eg.

 

Total savings per month =

CALCULATE(

SUM(TableOperationnel[Savings per year]),

FILTER(

ALL(TableOperationnel[Start Date]),

TableOperationnel[Start Date] <= MAX(TableOperationnel[Start Date])

),

ALL(TableOperationnel[Sector])

) / 12

 

 

Anonymous
Not applicable

Hi to both of you 🙂

Sorry for the delay, I was trying to solve it myself. Followed Microsoft's training about DAX and did a lot of troubleshooting. Still not able to make it work.


@Ashish_Mathur : Eventhoguh ID 3 has a 3 month duration, the savings are an estimate per year. Therefore, the total savings for ID 3 would be (Savings for ID 3/12)*3.

 

@d_gosbell The main issue here is to illustrate the savings for every month per sector. Let me show what I think the logic should be for a specific example (Let's say Jan 2019, for HR sector).

1. Loop through each row (Use SUMX?) in the TableOperationnel.
   IF ID's sector is equal to the HR sector
   AND
   IF [Start Date]< Jan 2019 < [End Date]
2. Divide savings of current ID by 12 AND add it to SUM of savings for JAN 2019 for HR sector

3. Go to the next sector

4. Go to next month

 

The logic above should be done For each month in the last 12 months (and 12 upcoming months), segmented by sectors.

 

You'll find the screenshot below of the chart when I tried the calculation you have suggested @d_gosbell :
Temp_PowerBI_Issue_2019-02-27_CHART.PNG


I have also attached the relationship logiTemp_PowerBI_Issue_2019-02-27_RELATIONSHIPS.PNG

 

Thanks again for your help folks!
Ben

Hi,

 

Do you want something like this.  You may download my file from here.  I am accumulating savings from the start of the Year (January 1), rather than the start of the period for which data is available.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks @Ashish_Mathur 
This chart is exactly what I am looking for.  I created similar tables to "Calendar" and a "Month" table, similar to what you did.

However, when I looked into your "Data" table, I see that you had 1 date per month per sector. I didn't see any formula for the  "Date" column. How did you create 12 lines (For all 12 months in a year) for every sector?
Was it through a formula?

Thanks again @Ashish_Mathur 

Ben

HTemp_PowerBI_Issue_2019-0301_TABLE.PNG

You are welcome.  Please click on the Query Editor and follow the steps show there.  Also, if my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I think the issue with all months pulling the same value is possibly because you have a bi-directional relationship from dates and sectors back to the operational table. The dates should definitely be a one way, one to many relationship to the operations table. I think if you fix up the relationships the graph will fix itself.

 

And if you are always dividing by 12 the order of the operations should not matter. (1/12) + (3/12) + (5/12) is mathematically the same as (1 + 3 + 5) / 12 (the order is only important if you don't have a common denominator)

Anonymous
Not applicable

Thanks d_gosbell!
It definitely works better. I have also changed the 'Tableoperationnel' to 'secteur' to a "one to many" relationship, as one process can only be within one sector/division.

Is there a way I can make the savings recurring though? Currently, it seems like the savings are divided by 12, but they do not appear for every month.

Total savings = 
CALCULATE(
    SUM(TableOperationnel[annual saving per process]),
    FILTER(
        ALL('TableOperationnel'[Start Date]),
        TableOperationnel[Start Date] <= MAX(TableOperationnel[Start Date])
        ),
ALL('TableOperationnel'[Secteur])
) / 12

When I tried to use Dates from the 'Dates' table, it generates an error.
Any idea how to make it the reccuring saving appear for every month that is between [Start Date] and [End Date]?

Thanks!
Ben



When I tried to use Dates from the 'Dates' table, it generates an error.


What was the error? Was it related to the bi-drectional relationship between the Dates and the Operational table? I'm not sure why you'd do that. Normally I'd have a 1 to many relationship from Dates to a fact/transaction table.

 

Any idea how to make it the reccuring saving appear for every month that is between [Start Date] and [End Date]?

You can't do a between join using relationships in a tabular model. But you can achieve the same effect by not having a join between your Date and Operational tables and doing the "between join" logic in a measure.

 

2019-03 cummulative between.png

I used the following 2 measures to achieve the output above.

 

Amt = sumx(
        filter(sales, 
            Sales[StartDate] < Max('Date'[Date]) 
            && Sales[EndDate] > min('Date'[Date])
        )
    , Sales[Amount])
Cummulative Amt = CALCULATE(
    [Amt] 
    , FILTER(ALL('date'), 'Date'[Date] <= max('Date'[Date]))
)

 

You can download a copy of this model from here

https://1drv.ms/u/s!AnSKp2UbblSYgbUQdmNBM926iPxTlQ

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors