topic Re: Need Daily Degree Day values from a list of Cumulative Degree Days in DAX Commands and Tips

Need Daily Degree Day values from a list of Cumulative Degree Days

BudMan512 — Wed, 10 Jan 2024 22:51:23 GMT

Hi,

I have a list of cumulative degree days by date in ascending order. The degree days accumulate beginning every July 1 until June 30 and then reset to 0 on July 1. I would like to determine the daily degree days that occured each day by finding the difference between the cumulative values. Here is a screenshot as an example of how the daily degree days are calculated.

Also, here is a link to an Excel file that can be used to develope the DAX.

https://ruralcomputers-my.sharepoint.com/:f:/g/personal/bingraham_rccbi_com/EsIpYKdyJZNGi9__tRP3-PkBZjtfSvv2R3IPlDyeCnIbBQ?e=l2EDeC

Thank you for your time.

Bud

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

Anonymous — Thu, 11 Jan 2024 06:14:50 GMT

Hi BudMan512

Please follow these steps:

1.Create a new column named Daily Degree days.

2.Then use the following DAX expression.

Daily Degree Days =
VAR PreviousDate = 'Table'[Date]-1
VAR PreviousCDD = CALCULATE(SUM('Table'[Cumulative Degree Days]),FILTER('Table', 'Table'[Date]= PreviousDate))
return
IF(PreviousCDD<>BLANK(), 'Table'[Cumulative Degree Days] - PreviousCDD)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

Jihwan_Kim — Thu, 11 Jan 2024 06:26:18 GMT

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

OFFSET function (DAX) - DAX | Microsoft Learn

Daily degree Days measure: = VAR _currentrowdate = MAX ( 'Calendar'[Date] ) VAR _recordstartdate = CALCULATE ( MIN ( Data[Date] ), REMOVEFILTERS ( 'Calendar' ) ) VAR _recordenddate = CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS ( 'Calendar' ) ) VAR _currentrowdata = SUM ( Data[Cumulative Degree Days] ) VAR _prevrowdata = CALCULATE ( SUM ( Data[Cumulative Degree Days] ), OFFSET ( -1, ALL ( 'Calendar'[Date] ), ORDERBY ( 'Calendar'[Date], ASC ) ) ) RETURN SWITCH ( TRUE (), MAX ( 'Calendar'[Date] ) <= _recordstartdate, BLANK (), MAX ( 'Calendar'[Date] ) > _recordenddate, BLANK (), MONTH ( _currentrowdate ) = 7 && DAY ( _currentrowdate ) = 1, 0, _currentrowdata - _prevrowdata )

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

BudMan512 — Thu, 11 Jan 2024 21:11:47 GMT

Jihwan_Kim

Thank you Jihwan for your reply. I tried your DAX expression but it did not work as expected.

There were two negative results. The first is the Sort Column is out of order. I'm not sure what is causing this. The second is the results of the calculation are not right. I was incorrect when I said that every July 1 the Cumulative Degree Days (Value) went to zero. It could be anything greater than zero and less than or equal to 1. Below is your DAX with modifications to fit my actual data. Also below is my Model.

Daily degree Days measure: =

VAR _currentrowdate =

MAX ( 'Date'[Date] )

VAR _recordstartdate =

CALCULATE ( MIN ( DD[DD Date] ), REMOVEFILTERS ( 'Date' ) )

VAR _recordenddate =

CALCULATE ( MAX ( DD[DD Date] ), REMOVEFILTERS ( 'Date' ) )

VAR _currentrowdata =

SUM ( DD[Value] )

VAR _prevrowdata =

CALCULATE (

SUM (DD[Value] ),

OFFSET ( -1, ALL ( 'Date'[Date] ), ORDERBY ( 'Date'[Date], ASC ) )

)

RETURN

SWITCH (

TRUE (),

MAX ( 'Date'[Date] ) <= _recordstartdate, BLANK (),

MAX ( 'Date'[Date] ) > _recordenddate, BLANK (),

MONTH ( _currentrowdate ) = 7

&& DAY ( _currentrowdate ) = 1, 0,

_currentrowdata - _prevrowdata

)

Here is the model

Thank you, I appreciate your help.

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

BudMan512 — Thu, 11 Jan 2024 22:00:11 GMT

Anonymous

Thanks so much for taking the time to help me. For some reason the DAX did not work correctly for me.

I have added more information using my own data to help get to the bottom of my issue.

Also the value every July 1 could go from 0 to 1 rather than 0 which is what I first reported.

Here is my interpretation of your script using my information.

DDD =
VAR PreviousDate = DD[DD Date]-1
VAR PreviousCDD = CALCULATE(SUM(DD[Value]), FILTER(DD,DD[DD Date] = PreviousDate))
RETURN IF(PreviousCDD<>BLANK(), DD[Value] - PreviousCDD)

Here are the results.

Here is the model.

I apologize if I bungled your recommendation. I appreciate you patience.

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

Anonymous — Fri, 12 Jan 2024 09:25:54 GMT

Hi BudMan512 ,

I’m sorry that my last response didn’t help you

May be you can try the following solution:

Create a New measure named Daily Degree days.

Then use the following DAX expression.

Daily Degree Days = var prviousCDD=CALCULATE(SUM('Table'[Cumulative Degree Days]), FILTER(ALLSELECTED('Table'), [Date]=MAX('Table'[Date])-1), CROSSFILTER('Date'[Date],'Table'[Date],None)) return IF(MAX('Table'[Date]) =DATE(YEAR(MAX('Table'[Date])),7,1),0,SUM('Table'[Cumulative Degree Days])-prviousCDD)

Here's my preview.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

BudMan512 — Fri, 12 Jan 2024 17:33:53 GMT

That worked perfectly. Thank you for the help. Very much appreceiated.

Re: Need Daily Degree Day values from a list of Cumulative Degree Days

BudMan512 — Fri, 12 Jan 2024 17:36:55 GMT

Thanks for your help. Even though I didn't use this as a solution I appreciate your time on the problem.

Regards