Forum Discussion
Help to construct Dax formula with conditional inputs
Do you really need the hourly data by day? From what I understand so far, this is not needed. I suggest you group by day and channel and sum the Value column. That way you have 1 number by channel by day.
I have created a summary table using DAX. You should load your data using Power Query so it is in summary format (not by hour) and not use what I have done. I have only done this because I don't have access to your source data.
Once I created the summary table, I copied my formula and applied the table name changes. There was a missing aggregator which I have fixed. It seems to work.
Here is the file
https://www.dropbox.com/s/t6p9f6mtwu12y2g/Datafile2.pbix?dl=0
Here is the corrected formula
test = CALCULATE ( CALCULATE ( ( SUM ( Summary[qty] ) - MAX ( RatesTable[USAGE FROM] ) ) * MAX ( RatesTable[RATES] ) + MAX ( RatesTable[$Amount] ), FILTER ( RatesTable, SUM ( Summary[qty] ) < RatesTable[USAGE TO] && SUM ( Summary[qty] ) >= RatesTable[USAGE FROM] ), FILTER ( RatesTable, MAX ( Summary[Date] ) < RatesTable[DATE TO] && MAX ( Summary[date] ) >= RatesTable[DATE FROM] ) ), FILTER ( ALL ( Calendar ), Calendar[MonthID] = MAX ( Calendar[MonthID] ) && Calendar[Date] <= MAX ( Calendar[Date] ) ) )
The error is because datatable[qty] operates without a row context and hence it can't be used without an aggregation function (my bad). Your update 1 solves this problem. If it works, it works.
Ok, but you want MTD. I think that changes everything. You will need a calendar table and you need to iterate over each day in the month, and for each day you will need to do the calculation. This is now past the tipping point where I would probably add a calculated column in the data table that returns the "rate" to charge for the row. This column is created using the same technique however a calc column has a row context. It is therefore much easier to calculate and return just the rate. Once the rate is in a column, the calculation qty * rate is very easy. This new calc column has a low cardinality and hence will compress well.
So the calc column would work like I showed in my banding example posted above. You apply the filters To the rates table as you have done so that only a single row is visible. Then return max of the tarrif column as the resulting calculated column result. From there you can just write a new measure
sumx(datatable,datatable[qty] * datatable[rate])
mtd should then work.
MattAllington wrote:... I would probably add a calculated column in the data table that returns the "rate" to charge for the row. This column is created using the same technique however a calc column has a row context. It is therefore much easier to calculate and return just the rate. Once the rate is in a column, the calculation qty * rate is very easy. This new calc column has a low cardinality and hence will compress well.
Remember for each row, there is a sliding tariff scale. So depending on the mtd value , multiple tariffs can be applied to one row of usage - so not sure returning a single tariff per row will work. I have attaached a link to an excel workbook on dropbox with sample data.
sample file
e.g for a day of say MTD 602 kWH
- first 299 will be calculated with a rate of $0.x (0-300)
- next 300 will be calculated with a rate of $0.y (300-600)
- final 3 kWh will have a rate of $0.z (600 +)
I understood that each row in your source data is evaluated against the usage consumption for that row. Now I understand that it is a cumulative tarrif to be applied for the total usage during the month. Let me look at the sample data
I have a table ( source data ) , which has dates and energy usage on those dates. Then I have a rate schedule I am looking to use to calculate the MTD cost of energy on each day. The rate schedule applies a different rate based on the MTD level of use - so for any particualr day, the MTD usage will be broken up into the usage buckets of 0-300, 300-600 and 600 + and the rates for each bucket applied accordingly to compute the total MTD cost. (plus VAT).
OK, Try adding some custom MTD time intelligence to the formula I provided earlier
This formula requires you to have a MonthID column in your calendar table. So the first month of the first year would be 1, the second month of the first year would be 2, the first month of the second year would be 13 and so on.
= CALCULATE ( SUM ( DataTable[qty] ) * MAX ( RatesTable[Rate] ), FILTER ( RatesTable, DataTable[qty] < RatesTable[To] && DataTable[qty] >= RatesTable[from] ), FILTER ( RatesTable, DataTable[date] < RatesTable[To Date] && DataTable[date] >= RatesTable[from date] ), FILTER ( ALL ( Calendar ), Calendar[MonthID] = MAX ( Calendar[MonthID] ) && Calendar[Date] <= MAX ( Calendar[Date] ) )
juju wrote:I have a table ( source data ) , which has dates and energy usage on those dates. Then I have a rate schedule I am looking to use to calculate the MTD cost of energy on each day. The rate schedule applies a different rate based on the MTD level of use - so for any particualr day,
The "issue" is that originally you said "daily level" and now you are saying "MTD". No harm done, just different.
Agreed - apologies. I realized I needed the MTD calculation halfway through this discussion. I will setup the calender table and apply the code and report back. Many thanks
MattAllington wrote:OK, Try adding some custom MTD time intelligence to the formula I provided earlier
This formula requires you to have a MonthID column in your calendar table. So the first month of the first year would be 1, the second month of the first year would be 2, the first month of the second year would be 13 and so on.
= CALCULATE ( SUM ( DataTable[qty] ) * MAX ( RatesTable[Rate] ), FILTER ( RatesTable, DataTable[qty] < RatesTable[To] && DataTable[qty] >= RatesTable[from] ), FILTER ( RatesTable, DataTable[date] < RatesTable[To Date] && DataTable[date] >= RatesTable[from date] ), FILTER ( ALL ( Calendar ), Calendar[MonthID] = MAX ( Calendar[MonthID] ) && Calendar[Date] <= MAX ( Calendar[Date] ) )MattAllington The above code still doesn't quite do what I am looking for. To illustrate, the code below works - if I hard code everything. I think your code does not account for the way each MTD number has to be broken up into the specific usage bands and a different rate applied for each band. Your code seems to look for one tariff that matches the MTD number - and applies one calculation, without regard for all the tiers in that one MTD number.
MTD Cost = IF ( DataTable[MTD Value] <= 300 , CALCULATE ( DataTable[MTD Value] * 0.9679), IF ( DataTable[MTD Value] > 300 && DataTable[MTD Value] <= 600, CALCULATE( 300 * 0.9679 ) + CALCULATE( ( DataTable[MTD Value]- 300 ) * 1.03 ), IF ( DataTable[MTD Value] > 600, CALCULATE( 300 * 0.9679 ) + CALCULATE( 300 * 1.03 ) + CALCULATE( ( DataTable[MTD Value]- 600 ) * 1.6251 ) , 0 ) ) )
- Yes, you are right, sorry. I think a slightly different approach is in order. In the rates table you should add a $Amount column as well as the rate columns you already have. The $Amount column will have the lump sum payable for the qty prior to the rate kicking in. So in the 0 - 300 row, the $Amount would be $0. In the 300 - 600 row it would be 300 times the rate payable up to 300 (payable as a lump). Doing it this way means you can do a more simple calc in the DAX Then you need to restrucutre my formula from before so that it first filters for the MTD calc, then after that it applies the rate calculations. Here is my guess (keeping in mind I don't have your data model infront of me.
=
CALCULATE (
CALCULATE (
(SUM ( DataTable[qty] ) - max(RatesTable[From])) * MAX ( RatesTable[Rate] )
+ MAX ( RatesTable[$Amount] ),
FILTER (
RatesTable,
Sum(DataTable[qty]) < RatesTable[To]
&& sum(DataTable[qty]) >= RatesTable[from]
),
FILTER (
RatesTable,
DataTable[date] < RatesTable[To Date]
&& DataTable[date] >= RatesTable[from date]
)
),
FILTER (
ALL ( Calendar ),
Calendar[MonthID] = MAX ( Calendar[MonthID] )
&& Calendar[Date] <= MAX ( Calendar[Date] )
)
)
Sorry I can't test it, but I think it is right. You may need to tweak it.
Let me know
Note I just made an edit MattAllington I have kicked it around a fair bit today - still cant get it to work. So attaching my datafile here with sample data. Maybe it will help. Many thanks. Much appreciated.
Your data model doesn't have a QTY column as mentioned in the other posts. I see a value column in the data table but it had integers from 0 to 16, which doesn't seem to be related to the probelm you have desribed.
The value column is my qty column. I change the code from [qty] to [value] when testing your code. Also the values are correct - from 0 to about 15 kWh per hour . So I am looking to use the daily MTD calculation for the [value] field to calculate the cost using the tariff structure .
Do you really need the hourly data by day? From what I understand so far, this is not needed. I suggest you group by day and channel and sum the Value column. That way you have 1 number by channel by day.
I have created a summary table using DAX. You should load your data using Power Query so it is in summary format (not by hour) and not use what I have done. I have only done this because I don't have access to your source data.
Once I created the summary table, I copied my formula and applied the table name changes. There was a missing aggregator which I have fixed. It seems to work.
Here is the file
https://www.dropbox.com/s/t6p9f6mtwu12y2g/Datafile2.pbix?dl=0
Here is the corrected formula
test = CALCULATE ( CALCULATE ( ( SUM ( Summary[qty] ) - MAX ( RatesTable[USAGE FROM] ) ) * MAX ( RatesTable[RATES] ) + MAX ( RatesTable[$Amount] ), FILTER ( RatesTable, SUM ( Summary[qty] ) < RatesTable[USAGE TO] && SUM ( Summary[qty] ) >= RatesTable[USAGE FROM] ), FILTER ( RatesTable, MAX ( Summary[Date] ) < RatesTable[DATE TO] && MAX ( Summary[date] ) >= RatesTable[DATE FROM] ) ), FILTER ( ALL ( Calendar ), Calendar[MonthID] = MAX ( Calendar[MonthID] ) && Calendar[Date] <= MAX ( Calendar[Date] ) ) )
MattAllington wrote:Do you really need the hourly data by day? From what I understand so far, this is not needed. I suggest you group by day and channel and sum the Value column. That way you have 1 number by channel by day.
MattAllington When calculating the cost , we dont really need the hourly data by day, however, hourly data is an important part of our analysis ( 24 hour usage profile ). So perhaps, I should create a calculated summary table ? Any issues about performance of calculated tables in case my dataset gets big?
I think it is OK to have it as long as you use it. But I didn't see any time of day data in your sample - just multiple copies of the same day. I suggest you split date and hour into 2 columns. Maybe just use an integer for hours (0 - 23).
Did the proposal I made work for you?Yes it did! I was just going to post that! Thank you!!
But I'd like to understand what is going on here : Thats the only part of the code I dont quite get. Especially the use of the $Amount .
CALCULATE ( ( SUM ( Summary[qty] ) - MAX ( RatesTable[USAGE FROM] ) ) * MAX ( RatesTable[RATES] ) + MAX ( RatesTable[$Amount] ),It's the MAX that makes it confusing. In the formula I wrote, there is only a single value for RatesTable[USAGE FROM], RatesTable[RATES] and RatesTable[$Amount]
If I wrote it like this, you would probably understand it.
CALCULATE ( (SUM ( Summary[qty] ) - RatesTable[USAGE FROM] ) * RatesTable[RATES] + RatesTable[$Amount]Add up the total Qty, subtract the usage from (which works out the incremental qty above the baseline, then multiply this incremental amount by the rate for the incremental, and finally add the lump sum. All good.
The problem is that DAX doesn't allow you to refer to what I call "Naked columns" in a DAX expression unless there is a Row Context (this is a topic in its own right). I have added MAX around each of these columns so they are no longer "Naked". But given I have already filted the table so there is only 1 row, it returns the value of that row. I could have used MIN, SUM, AVERAGE etc and they all would return the same result. Yes it is confusing, but once you get the concept you become more powerful.
Pretty advanced for a beginner! I need to read it a couple of times to get it. I think my Rates[$Amount] values are wrong . I don't get the significance of the Rates[$Amount] - what exactly it does.
Thanks again.
Well this is not really a dax pint, it is the calculation logic. rather than split the value into each bucket, subtract the amount and do the calculation, all you need to do is work out which band applies, and do a simple calc of $amount + rate X increment over base. In Australia, we use this for our tax tables.
https://www.ato.gov.au/rates/individual-income-tax-rates/
I have been trying to get you model to work for me using a summary based on the month and not just the day. I thought it should have been as easy as pulling my month column from my calendar table instead of the day. Any quick suggestions?
Hey Matt,
I am trying your model for our needs, We have a four tiered rate structure and your model looked like it would fit the bill. So I created a model with our data and keep getting the charge amount to read only one line in the rates table. Its not going though the rows and create a calulated value. Here is my model. I would appreciate any insights.
https://drive.google.com/file/d/1hRrsF2j_Ixm5PHSPrrekm8Rz4zFlmp_R/view?usp=sharing
