cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

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

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-PkB...

Bud

1 ACCEPTED SOLUTION
Community Support

Hi @BudMan512 ,

May be you can try the following solution:

1. Create a New measure named Daily Degree days.

1. 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.

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.

7 REPLIES 7
Super User

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
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

Helper V

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

Regards

Helper V

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.

Community Support

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)

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.

Helper V

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.

Community Support

Hi @BudMan512 ,

May be you can try the following solution:

1. Create a New measure named Daily Degree days.

1. 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.

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.

Helper V

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors