Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Sec...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

     

  • d_gosbell's avatar
    d_gosbell
    7 years ago


    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.

     

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 :


I have also attached the relationship logi

 

Thanks again for your help folks!
Ben

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    H

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      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.