Data grouping and missing month


#1

I have a data grouping which working perfect thanks to Sam’s video on you tube, I have 2 transaction tables called Claims and sales. They are connected through the date table.

Based on that grouping I have created couple of measures

Total By MIS = CALCULATE(Claims[ConvertedDataRowCount]|
    FILTER(values('Claims'[MISGroup])|
        COUNTROWS(
            FILTER('BinSelect'|
'Claims'[MISGroup]>=BinSelect[minvalueRange]&&
'Claims'[MISGroup]<BinSelect[MaxValueRange]

))>0))


Running Total By MIS = CALCULATE(
	[Total By MIS]|
	FILTER(
		ALLSELECTED('Claims'[MISGroup])|
		(ISONORAFTER('Claims'[MISGroup]| MAX('claims'[MISGroup])| DESC)
	)
))

The above 2 measures count the claims first for 0-3 and then with the running total counts 0-6, 0-12 and 0-24.
Based on the calculation I run another measure
IPTV = Divide(Claims[Running Total By MIS]|SUM(Sales[SALES])|0)*1000

All of these working fine without any problem. The issue I am having and trying to resolve it for the past 3 days is this
New%20Issue

if for 3 MIS (Month in service) for year 2017 and Month of April it has record in transaction table but for 6 MIS there is no record in the transaction table (Claims to be specific) it doesnt bring the value of 3 MIS. I just want it to bring back the value 3 MIS and place it in 6 as long as there are values in months after April for this example it has May present and same thing for June, July missing as August has value so it should replace the value for 3 MIS. the reason it is showing empty rows because I check the box "Show item with no data.
You guys have no clue how much I would appreciate the help.

BinSelect
This is the grouping table.


#2

Finding it difficult to see all the pieces here. The main confusion for me is where is the context for the grouping table in you actual table in Power BI.

It’s really understanding all the context which is difficult here, both in the formulas and the evaluation context in the page.

Would probably need to actually see the problem in a demo model to assist further.

My recommendation here is to really think through all the contexts yourself first. What’s coming from the table you’ve setup, then what is in the formula, and how they are working together. This is how you’ll find the problem most likely.


#3

Thank you Sam,
The grouping is in the model. It is calculated field that is provided to me. Called MISGroup. Which simply means that where the Month in service (MIS) fall into. it has null values as sometimes MIS is greater then 3 but less then 6 so the MISGroup value for it is null and as I said before its provided to me.

This is the data file after setting up all the filters.
MopMIS Broken Line.xlsx (56.4 KB)

This is the date file that is connected to the excel file on build_date.(Its also connected to sales table but that is not causing the problem so I am going to leave it out of it.)
Date.txt (353 Bytes)
The excel file is already filtered on Customer=‘GM’ , ModlYr=2018, Model=‘NQH’ and level1=“Leak”

I have also attached the graph that its creating and 3 MIS is missing months which I have highlighted.


Finally the table that it is outputting for the graph. I highlighted the missing months.
Outputdata

If you have any further questions please let me know. I tried my hardest and couldn’t figure it out.
Thanks in advance for looking into it.

I think the problem is that there is no data in Build_date in Claims table (excel sheet I provided) and its not printing those missing values I just want it bring the date from the dates table and if there is no previous value (for example 3 mis has no previous values for the month of August so it should be 0 otherwise for 12 MIS just take the value of 3 MIS) I know what the problem is I just dont know how to figure it out.


#4

I thought I should create an example that I can share.
Sample.pbix (174.3 KB)
Following is the desire output I am expecting. I dont want to do anything with 24 MIS. for 3 MIS in the missing months in the middle it should go down to 0 and for 12 MIS for missing month it should go to 3 MIS value for that particular month. But they should never cross each other .
Here is the desired output I am looking for


#5

If you are just looking to hard code a 0 this is not too difficult.

I’ve looked at the model and there’s a bit too much to get my mind around where this needs to go.

But all you need to do is go to you core calculation and wrap it in simple logic like the below.

IF( ISBLANK( measure ), 0, measure ).

That’s all you need to do and then you will get results

Something like this (I’m not getting the correct result, but this it the technique to use )

IPTV = 
VAR TotalSales = SUM( Sales[SALES] )

RETURN
IF( ISBLANK( TotalSales ),
    0,
    Divide( [Running Total By MIS], TotalSales * 1000, 0 ) )

Have a play around with this idea.


#6

Hi Sam
Thank you for looking into it. The only issue I see is that that will change IPTVfor 24 months as well, which I don’t want. How do I do what you suggested for only 3 and 12 months?


#7

I will greatly appreciate if you can spend more time on it. That was the whole point of me making the sample file and getting the membership.Let me try my level best to summarize it.
Claims and sales are connected to Date table on build date in claims and column called month in sales. Model year bring all the build dates that are in MISGroup 3,12 and 24. Let’s take 3 for example if it has June and August record then the July record should be 0. But for 12 months if it has August and October record so the value of IPTV should be equal to September of 3 MIS.
Rest of the tables you can ignore as they are supporting tables and I had to create the exact same report with lot less data so you can see what I am trying to achieve.


#8

Ok lots that can be improved on here to help you work this out. The setup just isn’t optimal and that’s why you’re finding it difficult to solve.

  1. Start using measure groups/tables like this

image

  1. Don’t place the name of the table before measures - it’s not required. You’ll run into issues like the below

image

  1. Also don’t believe you even need Years here. Also I would recommend using a better date table here. This one is limited.

image

  1. This to me seems unnessary and should be integrated into one table

image

  1. Really work on making small updates to make formulas read better.

image

change to more like this

image

  1. This to me is what the model should look like

image

  1. This doesn’t look right to me, shouldn’t this be 6? (i can’t change this myself)

image

  1. I also recommend when trying to work things out is place all your calculations in a table. This way you can see them one by one.

image

  1. Also I think the naming conventions of your measures could be better as well. I’ve updated some of them.

  2. I really didn’t have to do much I don’t think to get this to work

    IPTV =
    IF( ISBLANK( [Total Claims] ),
    0,
    Divide( [Running Total By MIS], [Total Sales], 0 ) * 1000)

image

I just improved on the various things mentioned above. This is the end result

Model link below
Sample (3).pbix (233.9 KB)