Cumaltive Diff with missing days

Question

I have an auto-generated dataset with a cumalative number. I used the method from&nbsp;v-ljerr-msft&nbsp;in&nbsp;Solved: Day to day difference in cumulative values DAX or ... - Microsoft Fabric Community&nbsp;to generate the daily difference - see my Usage column code below.My problem is that occasionally there is a gap in the data with missing days and this gives me an erroneous number.Any suggestions as to how to remedy?In my head, the best way would be to fill in missing data with half the difference.Alternatively, if there is no data, just subtract the difference from the next day where there is data.Hopefully this makes sense!&nbsp;&nbsp;&nbsp;Usage =var usagePreviousDay =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('ESB Data'[Reading]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL('ESB Data'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'ESB Data'[Date]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= EARLIER('ESB Data'[Date]) - 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp;&amp; 'ESB Data'[Type] = EARLIER('ESB Data'[Type])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;))RETURN'ESB Data'[Reading] - usagePreviousDay&nbsp;

pankajnamekar25 · Accepted Answer

Hello&nbsp;DonalMc&nbsp;&nbsp;try thid DAX codeUsage =VAR currentDate = 'ESB Data'[Date]VAR currentType = 'ESB Data'[Type]&nbsp;VAR previousDate =&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAX('ESB Data'[Date]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL('ESB Data'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'ESB Data'[Date] &lt; currentDate &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'ESB Data'[Type] = currentType&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )&nbsp;VAR previousReading =&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM('ESB Data'[Reading]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL('ESB Data'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'ESB Data'[Date] = previousDate &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'ESB Data'[Type] = currentType&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )&nbsp;RETURN&nbsp;&nbsp;&nbsp; IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT ISBLANK(previousReading),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'ESB Data'[Reading] - previousReading,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BLANK()&nbsp;&nbsp;&nbsp; )Thanks,&nbsp;Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

Forum Discussion

Cumaltive Diff with missing days

2 Replies

Related content

missing data

missing data

Generating Missing Data

Inferring Missing Columns and Imputing Missing Data

Missing information