Cumulative Total per group and month which fill the missing values from previous group and month

[mukhan169]

I have a column in the table that contains the value 3,6,12 and 24. This table is the transection table. I have a grouping table as such

Based on this grouping table on above table the transection table calculate running table. Measure 1 (I know bad name choice) just calculate the count of number of rows fall into that group and that month. Measure 2 calculates the running total. The issue I am having is when the group and month doesnt have count value of 0, Measure 2 doesnt pass the value of previous group/ month value.

Following are the measures.

ConvertedDataRowCount = COUNT(Claims[ID])

1 = CALCULATE(Claims[ConvertedDataRowCount ]|

FILTER(values('Claims'[MISGroup])|

COUNTROWS(

FILTER('BinSelect'|

'Claims'[MISGroup]>=BinSelect[minvalueRange]&&

'Claims'[MISGroup]<BinSelect[MaxValueRange]

))>0))

2 = CALCULATE(

    [1]|

    FILTER(

        ALLSELECTED('Claims'[MISGroup])|

        ISONORAFTER('Claims'[MISGroup]| MAX('Claims'[MISGroup])| DESC)

    )

It looks so simple but I am pulling my hair out for last 2 days and someone can help me in this regard it will be greatly appriciates.

[v-lili6-msft]

hi, @mukhan169 

You could try this way:

Create a MIS table then create a relationship with Claims table

MIS = VALUES(Claims[MIS] )

Then create measure 2 by this formula

2 = CALCULATE(
    [1],
    FILTER(
        ALLSELECTED(MIS[MIS]),
        ISONORAFTER(MIS[MIS] ,MAX(MIS[MIS]), DESC)
    ))

Darg MIS field from MIS table into visual

Result:

And here is a sample pbix file, please try it.

Best Regards,

Lin

[mukhan169]

@v-lili6-msft 

Thank you very much Lin,

It works partially, it is my fault for not being clear. If a month is missing in 3 MIS then it has to produce 0.

and the other thing is for 24 months only fill in the missing months (not to continue filling the months). Or to make it easy to understand just fill in 0 for 3 MIS for missing months and exclude 24 forcontinue on.

Let me try to explain the reason, MIS stands for Month in service. so if the date is less then 24 months (even 23) it doesnt go in 24. The way it is written now its keeps going. It be very helpful if you can help me Just add 0 to 3 MIS and do the same thing you are doing for 12 and exclude 24.

You have no idea how much I appriciate your help.

[v-lili6-msft]

hi, @mukhan169 

Add a date table, then create the relationship with "Claims table.

Use date filed from date table and for measure[IPTV] formula add a conditional " +0 " in it.

If not your case, please share your sample pbix file and expected output for us to have a test.

Best Regards,
Lin

[mukhan169]

hi @v-lili6-msft 

Thank you for your response. Please find attached pbix file. and also the picture of desired output. I am only worried about 3 and 12 MIS for now. The lines should NEVER cross each other. If 3 MIS is missing a month it will go to 0 if there are more month after. with the same logic if 12 month is missing the value it should go to 3 MIS value whatever that may bem,https://1drv.ms/u/s!AhEMn5zLqnzNdQug_GI3ffK6Xlc

Above is the link to pbix file as I cant upload the file directly.

Thank you again very much for your help

[v-lili6-msft]

hi, @mukhan169 

You may try this measure

Measure 9 = var _maxdate=CALCULATE(MAX(Claims[BUILD_DATE]),FILTER(ALLSELECTED('Dates'),'Dates'[Date]<MAX('Dates'[Date]))) return
IF(CALCULATE(MAX(Sales[Month]),ALLEXCEPT(Claims,Claims[MISGroup]))>= CALCULATE(MAX(Dates[Date])),
 IF(ISBLANK([IPTV]),CALCULATE([IPTV],FILTER(ALL('Dates'),'Dates'[Date]=_maxdate)),[IPTV]))

Result:

Best Regards,

Lin

[mukhan169]

@v-lili6-msft 

Hi Lin,

Thank you for the help. You almost got it I think I didnt explain the business rules properly. IPTV stands for incidents per thousand vehicles. They should never cross each other as IPTV for 12 MIS should always be either equal to or more then 3 MIS. and 24 always should be equal to or more then 12 MIS . It should also only fill in the missing months only and not extend months furthern. I am so sorry I am not trying to be difficult but those are the business rules. After I put your measure thats how the graph look like.

So 24 MIS service should have stopped at Feb as it was the last month in the data and there was no missing months in the middle. 12 MIS should follow 3 MIS and be 0 for November of 2016 and then should go up to December of 2016. And so on and so forth. Just dont add the months instead fill in the months that are missing in 12 MIS with the value of 3 MIS for same month and year and for 24 months follow the missing months to 12 MIS. And by missing months I mean if it has  value for January of 2018 and April of 2018 for 12 MIS, it should fill in February of 2018 and March of 2018 from 3 MIS. 

Again I apologize for not being clear earlier. You have no idea how much I appriciate your help.