How do I determine daily values from cumulative values in a table

[BudMan512]

I have created the included table containing a date range from 1/1/2014 to the present.

Cumulative Degree Days begin counting anew every July 1 and end June 30 and then repeats every 12 months.  That’s why the first screenshot shows 1326 degree days on Jan 1 and the second screenshot show it resetting on July 1.  Degree Days are used in the heating industry to help predict when a customer is due for a delivery, among other things.  The more degree days the more the furnace runs.

I am trying to determine the actual degree days that occurred each calenedar day.  In Excel its easy, add a third column and create a formula to subtract the cumulative DD for 1/1/14  from 1/2/14.  The difference is the degree days for 1/2/14.  In the included table that would be 34 degree days.  Then copy the formula down to 6/30/14 and start again every July 1.  So Excel I can do but I need some help with the DAX in Power BI.   I am new to Power BI but am taking some on line classes and haven’t gotten indepth with the DAX yet.  I would greatly appreciate any help given.  Thanks, Bud

[Ashish_Mathur]

Hi,

Assuming you have a Calendar Table which has a relationship with the Date column of your Data Table AND you have dragged the Date in your visual from the Calendar Table AND cumulative dgree days is a measure, write this measure

Measure = if(format(min(calendar[date]),"mmdd")="0701",[cumulative degree days],[cumulative degree days]-calculate([cumulative degree days],previousday(calendar[date])))

Hope this helps.

[BudMan512]

Hi Ashish I have added the date table and am using the date from that table in the visual.  However, the Cumulative Degree Day values were taken directly out of a table in our system and I need the daily values.  How would that affect the DAX you provided?  Thanks

[Ashish_Mathur]

Hi,

CDD = SUM(Data[cumulative degree days])

Degree days = if(format(min(calendar[date]),"mmdd")="0701",[CDD],[CDD]-calculate([CDD],previousday(calendar[date])))

Hope this helps.

[BudMan512]

Hi Ashish, thanks for hanging in there with me.  The measures are not working quite right.  The first year has problems and then when July 1 comes there is also a problem.  I have included screenshots and the DAX. Thanks

CDD = SUM(F_DEGREE_DAY[YTD Degree Days])

Degree Days- = IF(format(min('Date'[Date]),"mmdd")= "0701",[CDD],[CDD]-calculate([CDD], previousday('Date'[Date])))

[Ashish_Mathur]

Hi,

Ensure that you drag Date from the Calendar Table.  Also, replace CDD with YTD Degree Days.  My assumption is that YTD Degree Days is a measure.

Hope this helps. 

[BudMan512]

Hey,

'YTD Degree Days' is not a measure.  Values for that column come directly from the DegreeDay table.  So these measures aren't quite right.  I am trying to determine the daily values.  I am using the date field from the date table.

Thanks,

Bud

[Ashish_Mathur]

Hi,

I'll need to your file to work on.

[AlexisOlson]

You may be interested in my answer here:

https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969

Or the slightly more complicated grouped version here:
https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/219...

[BudMan512]

Thanks Alexis I will check that out.

[bcdobbs]

Are you able to share a demo pbix file?

General principal is straight forward but needs care when the year rolls over.

[BudMan512]

Sorry, I must not have permissions to share my file.