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

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

d_gosbell's avatar
d_gosbell
Super User
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