Counting Accumulation by month with withdrawal of expired record

[qpnna]

Greetings, 

I have a table with records characterized by a code and a date. For each code, I’ve created a specific "What If" parameter, which is a range of values between 1 and 12. The idea behind this implementation is to allow users to modify this parameter anytime. "A table could have been created instead, but my goal is to make the reporting easier to manipulate, especially for those who are not familiar with Power BI

My goal is to create a histogram that will show the accumulation of records by month, while taking the "What If" parameter into account for each code.

For example:
Let’s say I start in January. For the record with code A, the value of the associated "What If" parameter is 3. The accumulation should proceed as follows:

• January: 4 records for code A with matching dates -> Total = 4
• February: 3 records for code A with matching dates -> Total = 7
• March: 2 records for code A with matching dates -> Total = 9
• April: 5 records for code A with matching dates -> Total = 9 + 5 - 4 = 10, since 3 months have passed (we remove the accumulation from January)
• May: 6 records for code A with matching dates → Total = 10 + 6 - 3 = 13, since we remove the accumulation from February
• And so on...

This process must be applied for each code, where the given parameters are used to remove previous accumulations. In the end, this will essentially result in a cumulative sum.

If I must give a context, we can say that for a record, if its "What If" parameters is for instance 3, it means that from the moment it start, it will have an impact for 3 months. Hope it will help you better to understand the logic behind it.

I have tried many approach but seems to not be able to remove a specific amount between each month, my values remain always increasing, as if the core of my process isn't applied. 

Thanks in advance for any help and have a nice day. 

[johnt75]

You can use something like

Sum by code =
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        VALUES ( 'Table'[Code] ),
        VAR NumMonths =
            SWITCH ( 'Table'[Code], "A", [What if A], "B", [What if B] )
        VAR Result =
            CALCULATE (
                SUM ( 'Table'[Value] ),
                DATESINPERIOD ( 'Date'[Date], MaxDate, - NumMonths, MONTH )
            )
        RETURN
            Result
    )

This iterates over each visible code, gets the relevant what if parameter based on the code and then sums the values going back the requisite number of months.

[qpnna]

Question, about the code you've provided, I see thet the date from my main table is not being used, but only from the Calendar table I've generated. Is that intended ?

[qpnna]

Thanks for your reply, 

Regarding the table Date, should it be based on the lowest date and highest date of any record found on my main table ?
Something like : 

Date = CALENDAR(MIN(Table[date]), MAX(Table[date]))

[johnt75]

There's a bunch of articles and videos about creating a date table, but you can start with CALENDARAUTO(), that gets the first and last year from every date column in your model and generates a full calendar from that. You could have a look at Bravo for Power BI, that's a free tool which can generate a full date table for you.

Make sure to mark it as a date table too.

[qpnna]

Thanks for your help, I've successfully generated a Date table. From my first result, it seems that I need to adapt more thoroughly since my result are quite weird (constant value across month). Regarding the histogram I used then the generated Date Table for Axis X is that correct ?
Sorry by the way I won't be able to provide more until monday since I'm at home.

[johnt75]

link the date column from your date table to the date column in your fact table. that should fix the issue of you getting the same value for every month.

[qpnna]

Thank you for your reply, 

Now it seems that instead of having a cumulative value while taking into account my What If parameters, I obtain the SUM of record by month.

My code is as follow, from your reply of Friday : 

CumulCP = 
VAR MaxDate = MAX('Calendar'[Date])
RETURN
    SUMX(
        VALUES ('Table_Record'[code]),
        VAR NumMonths = 
            SWITCH(
                TRUE(),
                SELECTEDVALUE('Table_Record'[code]) = "A", [Value A_WhatIfParameter], 
                etc, ..., 
                1
            )
        VAR CumulResult = 
            CALCULATE(
                COUNTAX('Table_Record', 'Table_Record'[id]),
                DATESINPERIOD('Calendar'[Date], MaxDate, -NumMonths, MONTH)
            )
        RETURN
            CumulResult
    )

 
Notice that I've changed the SUM for a COUNTAX, since my goal is to return the number of record that match my query. 
I've also linked the column Date from my table Calendar with the column Date from my Table_Record, as a 1 to many relationship.

Thanks again for your help.

[johnt75]

Have you marked your date table as a date table ? If you right click on the date table, there is an option in the context menu.

A small point, but you might find COUNTROWS performs better than COUNTAX as long as you don't have any entries where 'Table_Record'[id] is blank.