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
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
Solved! Go to 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.
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
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
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.
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.
Very good. Let me have a look at it tomorrow morning (I guess we have around 10 hrs time difference)
@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?
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
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.
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
I am all set. Thanks for your patience and I very much appreciate all you did for me.
Regards,
Bud
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |