Forum Discussion
Cumulative Recurring SUM per Sector
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.
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
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)
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])
) / 12When 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.
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