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

[BudMan512]

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

Thank you for your time.

Bud

[Jihwan_Kim]

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
    )

[BudMan512]

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

Regards

[BudMan512]

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

[v-yohua-msft]

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.

[BudMan512]

@v-yohua-msft 

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.

[v-yohua-msft · Accepted solution]

Hi @BudMan512 ,

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

May be you can try the following solution:

1. Create a New measure named Daily Degree days.

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

[BudMan512]

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