cancel
Showing results for
Did you mean:
Helper III

Convert Cumulative Degree Days to Daily Degree Days

Hi All,

I have a two-column report.  The first is the Date beginning 1/1/2015 and going through the current date.  The second column is the Cumulative Degree Days as of any date.

In this case a degree day is a unit used to determine the amount of fuel it takes to heat a building.  The more degree days, the colder it is and the more fuel it takes.

I am trying to determine the number of Degree Days that have occurred in each day rather than the cumulative for the heating season to date.

The Heating Season runs July 1 through June 30.  At which point the Cumulative Amount resets to 0 or 1.

Not sure if this maters but each heating season includes July - December of one year and January - June of the next year.

Any help would be appreciated.

Thanks,

Bud

1 ACCEPTED SOLUTION
Super User

Hi @BudMan512
As I can see that the date column in the fact table has unique values. This is one of the few cases were the the date table shall not be required. I assume I have the full picture which is a thing that I'm not sure of, so please advise.

Based on above I proposed a a calculated column being simplier to compute. Let me know if this does not make sense with the real data and clarify the reasons.

Please refer to my proposed solution in the attached sample file.

12 REPLIES 12
Super User

two calculated columns

Season =
YEAR ( EOMONTH ( 'Table'[Date], -7 ) + 1 )

DDD =
VAR CurrentDate = 'Table'[Date]
VAR CurrentCDD = 'Table'[CDD]
VAR BuildingYearTable =
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[Building ID], 'Table'[Season] )
)
VAR PreviousRecord =
TOPN (
1,
FILTER ( BuildingYearTable, 'Table'[Date] < CurrentDate ),
'Table'[Date]
)
VAR PreviousCDD =
MAXX ( PreviousRecord, 'Table'[CDD] )
RETURN
CurrentCDD - PreviousCDD

Helper III

Hi Tamerj1,

Thanks for the reply. I am afraid I have misled you with the below definition.

"In this case a degree day is a unit used to determine the amount of fuel it takes to heat a building.  The more degree days, the colder it is and the more fuel it takes."

This was intended to explain what a degree day was and was not intended to indicate there was a building Table.  I tried removing the references to  Var BuildingYearTable and the field Building ID but the program didn't like that.  My apologies but would you mind doing the second column without the building references?  The first column worked perfectly.  Thank you, Bud

Super User

DDD =
VAR CurrentDate = 'Table'[Date]
VAR CurrentCDD = 'Table'[CDD]
VAR SeasonTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Season] ) )
VAR PreviousRecord =
TOPN ( 1, FILTER ( SeasonTable, 'Table'[Date] < CurrentDate ), 'Table'[Date] )
VAR PreviousCDD =
MAXX ( PreviousRecord, 'Table'[CDD] )
RETURN
CurrentCDD - PreviousCDD

Helper III

@tamerj1

I would have sent it sooner but had to arrange it with our Admin.

Here are  my results from the last try.

Here is the DAX I used.

DDD =

VAR CurrentDate = 'Date'[Date]

VAR CurrentCDD = 'F_Degree_Day'[CDD]

VAR SeasonTable =

CALCULATETABLE ( 'F_Degree_Day', ALLEXCEPT ( 'Date', 'Date'[Season] ) )

VAR PreviousRecord =

TOPN ( 1, FILTER ( SeasonTable, 'Date'[Date] < CurrentDate ), 'Date'[Date] )

VAR PreviousCDD =

MAXX ( PreviousRecord, 'F_Degree_Day'[CDD] )

RETURN

CurrentCDD - PreviousCDD

I appreciate your assistance very much.

Super User

Very good. Let me have a look at it tomorrow morning (I guess we have around 10 hrs time difference)

Helper III

@tamerj1

Thanks I appreciate it. I am in Maine,  USA, Eastern time zone.

Super User

@BudMan512
That makes it 8 hours behind Dubai. Unfortunately, the link is not working for me. Not sure what is the reason but could be that the domain itself is blocked in UAE. Would you please try something else?

Helper III

That link didn't work for me either, when I tried to open it on the Forum.

They gave me another link which is working for me so hopefully will work for you as well.

Thanks

Super User

Hi @BudMan512
As I can see that the date column in the fact table has unique values. This is one of the few cases were the the date table shall not be required. I assume I have the full picture which is a thing that I'm not sure of, so please advise.

Based on above I proposed a a calculated column being simplier to compute. Let me know if this does not make sense with the real data and clarify the reasons.

Please refer to my proposed solution in the attached sample file.

Helper III

Thanks so much the DDD calculated column it is working perfectly.  Thank you for your patience.

If I may,  there is one small issue left regarding the Season calculation.

Please refer to the below table.

The ending Season for 6/30/2016 should be 2016 not 2015.

And going forward from 7/1/2016 it should be 2017 rather than 2016.

In other words each 'Season' value should be increased by 1 year.

I tried playing with the numbers in the DAX script  but couldn' get it to behave.

Thank you again,

Bud

Super User

Why don't you just plus 1? Am I missing something?

Helper III

I am all set.  Thanks for your patience and I very much appreciate all you did for me.

Regards,

Bud