cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## 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 V

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 V

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

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

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 V

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 V

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

Regards,

Bud

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors