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.

[qpnna]

Yes the Date Table is marked as a Date, with the good format. 
My result still looks like sum for each month, also I don't know why but it seems that my parameters does not impact the result. I've made sure there is no mistake regarding the syntax.

[johnt75]

Can you share a PBIX ? You could post a link to Google Drive, OneDrive or similar.

[qpnna]

I would love to but I'm afraid I won't be possible since the data source is within the enterprise and I doubt I would be able to obtain the credential for it, let alone giving access to it. 😅

[johnt75]

I think all I can do is explain exactly what the code is doing and assumptions I am making. Check that these match with what you are expecting.

I am assuming that you have a visual ( table, matrix, chart ) with the field(s) from your calendar table and the 'Table_Record[code] column, and the measure we are working on.

I am also assuming that 'Table_Record[id] is a column which uniquely identifies a row, i.e. there is one value of [id] for each row, and no [id] occurs more than once.

Finally, I am assuming that the 1-many relationship from the calendar table to 'Table_Record' is active, and single direction.

The code starts by storing the latest date from the calendar table, given the current filter context in the visual.

It then iterates over all visible values of 'Table_Record'[code]. If you have this column in the visual then that really only matters in the total, which wouldn't be visible anyway in a chart. For the case when the code is in the filter context, from the visual, then there would be only 1 value visible anyway.

It then uses a switch statement to work out the number of months which should be visible based on the currently visible code. You mention that this is not working at the moment, that your parameters are not having an effect. Try setting the NumMonths variable to a fixed value, e.g. 6, and see what results that gives. Then change it to e.g. 3 and make sure that the results are different.

The code then generates a list of dates starting from the previously stored latest date and going back the number of months defined by the switch statement. It uses this list of dates as a filter on the calendar table, which in turn filters 'Table_Record', and counts the number of nonblank values of 'Table_Record'[id] which are visible once the date filter has been applied. This is the result that is returned.

[qpnna]

Thank you for your reply,

In fact, my parameters does work, but since the result from the measure created for this seems to only show the sum for each month independently, I assumed that for some codes there were no difference at all. 

The visual that I expect is a histogram, only the date (months and years) are represented for Axis X, so since what we want is the total there is no need to display each code. 

The result I'm getting make no sense because there is some decreases between months even if all my parameters are set to twelve, which should cover a full year. I might be wrong but I believe my loop does not carry the sum for each iteration, or maybe somehow it resets. I've come to this conclusion because raising a parameter for a code which only appear in a specific month will only raise the concerned month. 

Edit: Sorry for the multiple answer, for some reason it takes almost 4 hours for my response to be posted.

[johnt75]

I think I see the problem. SELECTEDVALUE is not going to work, because that only works in a filter context and we have a row context due to the SUMX iterator.

In the SWITCH statement replace

SELECTEDVALUE('Table_Record'[code])

with 

'Table_Record'[code]

I think that will fix it.

[qpnna]

Hmm I think I've once typed without SELECTEDVALUES, but without it Power BI throws me an error, saying that it failed to find the column for my table.

[johnt75]

Does it still give an error now? It shouldn't inside an iterator.

[qpnna]

Yes for some reason now it allows me to write the Switch case without SELECTEDVALUE. Regarding my result, still trying to figure wether my results are relevent or not. 

I have some doubt because in this picture all my parameters are set to 12 (12 months), so normally my result should be always ascending.

Edit : This was the default result, the difference here is that for the Switch I used SELECTEDVALUES

[johnt75]

The figures in Dec 2022 should be the same for both, and clearly they are not. I think you will need to explore your data to see what is going on.

The first thing I would check is if there are duplicate values for 'Table_record'[id]. If there are then I would replace the COUNTAX with COUNTROWS.

The next step I would take is to compare values for each code individually. Open DAX Query View and run

DEFINE
    VAR _12Months =
        TREATAS (
            { ( 12, 12, 12 ) },
            'Parameter A'[Value],
            'Parameter B'[Value],
            'Parameter C'[Value] // etc
        )
    VAR Dec2022 =
        TREATAS ( { DATE ( 2022, 12, 31 ) }, 'Calendar'[Date] )

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Calendar'[Year month],
        'Table_record'[code],
        Dec2022,
        "1 month", [CumulCP],
        "12 months", CALCULATE ( [CumulCP], _12Months )
    ),
    [1 month] <> [12 months]
)
ORDER BY
    'Calendar'[Year month],
    'Table_record'[code]

This will show any codes where the calculation differs between using 1 month as a parameter and using 12 months. If you find any codes different then look at the underlying data in table view for that code for Dec 2022 and see if you can spot any anomalies.

[qpnna]

Yes the goal is to represent the workload through a histogram. According to my database the result should be somewhat continous, assuming all parameters are equals.

 
The total is what matter so there is no representation for each code. The calendar table is linked to the date column of my record table correctly, and is calculated with the MIN and MAX of said table. The measure created sometimes return only the sum for each month, otherwise I usually get an accumulation without taking into account the parameters, which eliminate the purpose. 

I'll try to work around the code you've provided friday and see if I can manage to solve the problem, it might be a problem on my end. 

Thanks again for your help, appreciate it, I've been working my brain out on this but at least according to you it is feasible within the reach of Power BI 😄.