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
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.
This is the grouping table.