Craigc3814
Board Regular
- Joined
- Mar 7, 2016
- Messages
- 217
This is going to be a really complicated issue to explain but I am going to do my best. I am using PowerPivot for measures and PQ to clean the data.
I am building a tool that projects energy charges for the next 12 months at facilities that my company owns.
The way it works is the highest usage month sets our rate for the next 12 months, so if we have a ton of usage in February we know that for the next 11 months we are going to be billed for February's usage. My issue is once Feb falls off I need to then find the second highest month of usage and factor it in.
It is currently October so we only have 3 months left of paying for February. Once February falls off is when I am really struggling. I need a measure that says OK February falls off in January then you are going to start being billed for the second highest usage month AFTER the month of February. So if the next highest month was March I am going to pay for one month of March's usage then it falls off. I then need to find the 3rd highest usage month and project its charges out until 12 months are accounted for.
Here is an example of what the data currently looks like (Pivot Table)
10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18
Asset Name
Asset Address
Sum of Usage 5,781 5,007 5,960 5,198 6,333 5,930 5,359 5,400 5,145 6,199 7,407
Rank 6 11 4 9 2 5 8 7 10 3 1
Months Rem 0 1 2 3 4 5 6 7 8 9 10
This is an easy example because the most recent month is the highest charge so I would look for rank 1 realize it happened in our most recent billing month (Data only gets updated once a month) then project that usage out as the max for the next 10 months every month that happened before 8/1/18 is irrelevant because those usage numbers will fall off before my current highest month will
Here is a much harder example thats in my data
10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18
Asset Name
Asset Address
Sum of Usage 10,590 13,840 10,525 11,543 12,758 13,502 12,692 11,923 12,302 8,096 6,385
Rank 8 1 9 7 3 2 4 6 5 10 11
Months Rem 0 1 2 3 4 5 6 7 8 9 10
So here I would need to factor in 11/1/17 for 1 month then factor in 13,502 for 4 months (1 month falls off before 11/1/17 data expires) then 1 month of 12,692 (2/1/18 does not get used because 3/1/18 comes after it, making 4/1/18 the next highest but by the time 3/1/18 falls off there is only 1 month left of it) then 6/1/18 gets used for several months before falling off an 8/1/18 becomes the highest month.
I have been struggling with this for a bit and I am starting to think that DAX is not my answer.
I am building a tool that projects energy charges for the next 12 months at facilities that my company owns.
The way it works is the highest usage month sets our rate for the next 12 months, so if we have a ton of usage in February we know that for the next 11 months we are going to be billed for February's usage. My issue is once Feb falls off I need to then find the second highest month of usage and factor it in.
It is currently October so we only have 3 months left of paying for February. Once February falls off is when I am really struggling. I need a measure that says OK February falls off in January then you are going to start being billed for the second highest usage month AFTER the month of February. So if the next highest month was March I am going to pay for one month of March's usage then it falls off. I then need to find the 3rd highest usage month and project its charges out until 12 months are accounted for.
Here is an example of what the data currently looks like (Pivot Table)
10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18
Asset Name
Asset Address
Sum of Usage 5,781 5,007 5,960 5,198 6,333 5,930 5,359 5,400 5,145 6,199 7,407
Rank 6 11 4 9 2 5 8 7 10 3 1
Months Rem 0 1 2 3 4 5 6 7 8 9 10
This is an easy example because the most recent month is the highest charge so I would look for rank 1 realize it happened in our most recent billing month (Data only gets updated once a month) then project that usage out as the max for the next 10 months every month that happened before 8/1/18 is irrelevant because those usage numbers will fall off before my current highest month will
Here is a much harder example thats in my data
10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18
Asset Name
Asset Address
Sum of Usage 10,590 13,840 10,525 11,543 12,758 13,502 12,692 11,923 12,302 8,096 6,385
Rank 8 1 9 7 3 2 4 6 5 10 11
Months Rem 0 1 2 3 4 5 6 7 8 9 10
So here I would need to factor in 11/1/17 for 1 month then factor in 13,502 for 4 months (1 month falls off before 11/1/17 data expires) then 1 month of 12,692 (2/1/18 does not get used because 3/1/18 comes after it, making 4/1/18 the next highest but by the time 3/1/18 falls off there is only 1 month left of it) then 6/1/18 gets used for several months before falling off an 8/1/18 becomes the highest month.
I have been struggling with this for a bit and I am starting to think that DAX is not my answer.
