Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BudMan512
Helper V
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.

 

BudMan512_0-1684864358062.png

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

BudMan512_1-1684864555952.png

Any help would be appreciated.

Thanks,

Bud

 

 

 

1 ACCEPTED SOLUTION

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.

1.png

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

Hi @BudMan512 

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

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

@BudMan512 

Please try

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

@tamerj1

Thanks for your last reply.  

I am still having some trouble and thought a link to the report might help.http://downloads.rccbi.com/powerbi/dd_table.pbix 

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

Here are  my results from the last try.

BudMan512_0-1685028139517.png

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.

@BudMan512 

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

@tamerj1

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

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

@tamerj1 

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

https://filebin.net/qwuqrbdg2uifqg9i/dd_table.pbix 

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.

1.png

@tamerj1 

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

 

 

BudMan512_0-1685460177681.png

 

@BudMan512 

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

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

Regards,

Bud

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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