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

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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):


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