Rate change calculation DAX

Rate change calculation DAXJump to Best AnswerFollow
Vinu Hari
Vinu HariSep 05, 2022 11:45 PM
Hi Can some one help me to fix the issue I need the cost to be computed based on the rate between start …
1. Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 05, 2022 11:45 PM | view attached
Hi Can some one help me to fix the issue

I need the cost to be computed based on the rate between start and end period of each change against F-Data and F_Type.

Attaching the file

Thanks

——————————
FA
——————————
Attachment(s)

pbixGH1-test.pbix

2. RE: Rate change calculation DAX

Bronze Contributor
James Watts
Posted Sep 06, 2022 02:49 AM
You need to publish details of the calculations.

——————————
James Watts
Business Intelligence Analyst
——————————

 

3. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 06, 2022 12:05 PM
Hi, I did not made any DAX for measure, I need one

The attachment is only having simple data

——————————
FA
——————————

 

4. RE: Rate change calculation DAX

Bronze Contributor
James Watts
Posted Sep 07, 2022 05:14 AM
You need to provide details of what the calculation should be though.

——————————
James Watts
Business Intelligence Analyst
——————————

 

5. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 07, 2022 01:02 PM
Hi James, This is the required output to achieve

1) A simple calculation = F_cont x BKR (exchange rate) x Rates (Rate against F_type & F_cont)
2) You will notice the rate is getting changed eff 01/9/22 and a new rate will be applied to F_type.
In simple, the amt to be calculated based on prevailing rate against F_type & F_cont

Thanks

 

——————————
FA
——————————

 

6. RE: Rate change calculation DAX

Kaz Shakir
Posted Sep 07, 2022 07:36 PM | view attached
@Vinu Hari,
I took a look at your .pbix file, and I’ve got a few suggestions for you.

First, I would simplify your data model, so that it looks like the picture below. Eliminate the many-to-many relationships, and any bi-directional relationships. I don’t think you really need them.
​
Then, in order to create the table you described above, I would create several different measures.
1. First for the column you marked as “Ref Table F_cont”, I created a measure called “Sum Of F-Data Count”, that simply looks like this:
Sum Of F-Data Count = SUM(‘F-Data'[Count])​
2. Next, for the column you marked as “Ref Table BKR”, I created a measure called “Sum Of BKR-Act”, like this:
Sum Of BKR-Act = SUM(BKR[BKR-Act])​
3. For the column you labeled “Ref Table Rate Master For List Rates”, I created a measure called “Sum Of List Rate”. This one is a little more complicated because you grab only the values of List Rate that are appropriate for the date that is showing in the current filter context.
Sum Of List Rate =
VAR _currentDate =
MIN(‘Calendar'[Date])
RETURN
CALCULATE(
SUM(‘Rate Master'[List_rate]),
_currentDate >= ‘Rate Master'[Rate_start],
_currentDate <= ‘Rate Master'[Rate_end]
)​
4. And then for the “Amt” column, I simply created a measure to perform the math you described:
Amt =
DIVIDE([Sum Of F-Data Count] * [Sum Of BKR-Act] * [Sum Of List Rate], 100)​
5. Finally, in order to create the table you show in your last post, we need to limit the dates we show to only those where there is data contained in the F-Data table. To do this, I created a measure called “Number Of Rows in F-Data”, and I use this measure as a filter for the visual.
Number Of Rows In F-Data = COUNTROWS(‘F-Data’)​
And the resulting visual looks like this:

Now, my math did not seem to work out the same as what you posted in your last post, but I know the values I have are based on the data from your .pbix. Since you understand your data better than I do, perhaps you can figure out where I went wrong.

I’m attaching the .pbix file for your review.

Hope this helps.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixHari_example_v1.pbix

7. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 07, 2022 11:16 PM
Dear Kaz, Wonderful, this is working, thanks for the help

——————————
FA
——————————

 

8. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 07, 2022 11:17 PM
Just a simple one – how to avoid calculating “sum of list rate’ at the bottom.

——————————
FA
——————————

 

9. RE: Rate change calculation DAX

Kaz Shakir
Posted Sep 08, 2022 07:53 PM
@Vinu Hari,
I think you are referring to the grand total at the bottom of the table. If that’s the case you can just wrap the calculation in an IF statement and check to see if more than one date exists. This works because on the total row, there is no filter for the calendar table, so it’s as if all the dates exist on that row. Give this a try and let me know if that gives you the result you wanted:
Sum Of List Rate =
VAR _currentDate =
MIN(‘Calendar'[Date])
RETURN
IF(
HASONEVALUE(‘Calendar'[Date]),
CALCULATE(
SUM(‘Rate Master'[List_rate]),
_currentDate >= ‘Rate Master'[Rate_start],
_currentDate <= ‘Rate Master'[Rate_end]
),
BLANK()
)​

Also, I should have mentioned this in my first post, you could also accomplish these results by using calculated columns added to the F-Data table. Here are the three calculated columns you could add to that table:
Associated BKR-Act =
VAR _currentDate = ‘F-Data'[Month]
VAR _currentStation = ‘F-Data'[Station]
RETURN
CALCULATE(
SUMX(
BKR,
BKR[BKR-Act]
),
FILTER(
‘Calendar’,
‘Calendar'[Date] = _currentDate
),
FILTER(
‘CC Master’,
‘CC Master'[Station] = _currentStation
)
)

Associated List Rate =
VAR _currentDate = ‘F-Data'[Month]
VAR _currentFType = ‘F-Data'[F_type]
RETURN
CALCULATE(
SUM(‘Rate Master'[List_rate]),
FILTER(
AC_config,
AC_config[A_config] = _currentFType
),
_currentDate >= ‘Rate Master'[Rate_start],
_currentDate <= ‘Rate Master'[Rate_end]
)

AMT = DIVIDE(‘F-Data'[Count] * ‘F-Data'[Associated BKR-Act] * ‘F-Data'[Associated List Rate], 100)​
​Then you could have a visual that’s only using columns from the F-Data table, like this:
​
Each approach has it’s benefits and drawbacks, and you will have to decide what works best for you.

Hope that helps.
Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

10. RE: Rate change calculation DAX

Kaz Shakir
Posted Sep 08, 2022 09:09 PM | view attached
attachment

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixHari_example_v2.pbix

11. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 09, 2022 12:47 PM
Finally,

1) I have F-count for the period i.e. up to Mar’23 is available.
2) BKR data (associated BKR act) is available only up to Sep’22

– DAX needed to repeat the last BKR rate (i.e. Aug’22) for the period from Oct’22 to Mar’23 to compute AMT

 

——————————
FA
——————————

 

12. RE: Rate change calculation DAX

Kaz Shakir
Posted Sep 09, 2022 05:26 PM
@Vinu Hari,
I’m sorry I don’t understand. In the same data that you uploaded, there are values in the BKR table for several dates after Sep 22. Could you explain your question differently?

Kaz.​

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

13. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 10, 2022 09:48 AM | view attached
Posting a new pbix file, where you will notice, the AMT is not calculated because of missing BKR.
I would like to repeat the last available BKR rate, where rates are not available against stations

NOTE – Avoid sum for coloumn ExcH rate & Rate

Thanks

——————————
FA
——————————

Attachment(s)

pbixGHA test.pbix

14. RE: Rate change calculation DAX Best Answer

Kaz Shakir
Posted Sep 10, 2022 05:14 PM
Edited by Vinu Hari Sep 28, 2022 05:37 AM | view attached
@Vinu Hari,
In order to get rid of those totals for the Exch Rate and Rate columns, you will need to revert to the using measures in the table-visual rather than the calculated columns. We will still use the calculated columns to arrive at the AMT value, but you will use the measures for the table-visual.

And in order to get the Exchange Rate to pick up the latest available exchange rate associated with a given date, you will need to take two steps.
First, you need to disconnect the BKR table from the calendar table – that relationship does not really exist in your data model. (and, frankly, you might not need the calendar table at all – it does not seem to serve any purpose in this example). Now your data model will look like this:
​
Next, re-write the Sum Of BKR-Act measure, so that the formula looks like this:
Sum Of BKR-Act =
VAR _currentDate = MIN(‘F-Data'[Month])
VAR _currentStation = MIN(‘F-Data'[Station])
VAR _closestExchangeRateDate =
CALCULATE(
MAX(BKR[Date]),
BKR[Date] <= _currentDate,
BKR[Station] = _currentStation
)
RETURN
CALCULATE(
SUM(BKR[BKR-Act]),
BKR[Date] = _closestExchangeRateDate,
BKR[Station] = _currentStation
)​
The new variable, _closestExchangeRateDate, looks at all of the values in the BKR table for the current station, and that have dates that are either less than or equal to the current date in the filter context, and it picks the most current date of those. This variable will then be used to filter the BKR table in the final result to arrive at the exchange rate. An important item to keep in mind is that if there are two rows in the BKR table that have the same station and the same date, then those exchange rates will get added together with this formula. I am assuming that such a situation will not happen in the BKR table.

Then, we want to make the calculated column similar, so that formula becomes:
Associated BKR-Act =
VAR _currentDate = ‘F-Data'[Month]
VAR _currentStation = ‘F-Data'[Station]
VAR _closestExchangeRateDate =
CALCULATE(
MAX(BKR[Date]),
BKR[Date] <= _currentDate,
BKR[Station] = _currentStation
)
RETURN
CALCULATE(
SUM(BKR[BKR-Act]),
BKR[Date] = _closestExchangeRateDate,
BKR[Station] = _currentStation
)​
And then, we need to adjust the Sum Of List Rate measure, so that it looks like this:

Sum Of List Rate =
VAR _currentDate = MIN(‘F-Data'[Month])
VAR _currentFType = MIN(‘F-Data'[F_type])
VAR _currentstn = MIN(‘F-Data'[Station])
RETURN
CALCULATE(
SUM(‘Rate Master'[List_rate]),
FILTER(
AC_config,
AC_config[A_config] = _currentFType
),
‘Rate Master'[Station] = _currentstn,
_currentDate >= ‘Rate Master'[Rate_start],
_currentDate <= ‘Rate Master'[Rate_end]
)
And similarly, the calculated column will become this:

Associated List Rate =
VAR _currentDate = ‘F-Data'[Month]
VAR _currentFType = ‘F-Data'[F_type]
VAR _currentstn = ‘F-Data'[Station]
RETURN
CALCULATE(
SUM(‘Rate Master'[List_rate]),
FILTER(
AC_config,
AC_config[A_config] = _currentFType
),
‘Rate Master'[Station] = _currentstn,
_currentDate >= ‘Rate Master'[Rate_start],
_currentDate <= ‘Rate Master'[Rate_end]
)
And then your table-visual, would look like this:

Does that accomplish what you need?

Kaz.

 

 

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixHari_example_v3.pbix

15. RE: Rate change calculation DAX

Kaz Shakir
Posted Sep 11, 2022 05:05 PM | view attached
@Vinu Hari,
I had the file open on my desktop today, and noticed that I gave you some bad advice in my last message.​ My apologies.

Since we have created the new calculated columns, and they work well, we can actually just reference those in the measures – that way we are not duplicating those calculations, which will make it easier to maintain in the future (if the calculation were to change, you would only need to change it in one place).

I would create two new measures: one to calculate the average of the “Associated BKR-Rate” column, and another to calculate the average of the “Associated List Rate” column, like so:
Avg Of Associated BKR-Act =
IF(
ISFILTERED(‘F-Data'[Month]),
AVERAGE(‘F-Data'[Associated BKR-Act]),
BLANK()
)

Avg Of Associated List Rate =
IF(
ISFILTERED(‘F-Data'[Month]),
AVERAGE(‘F-Data'[Associated List Rate]),
BLANK()
)​
note: the IF statement and the use of the ISFILTERED condition is so that the measure returns a blank on the total row. If you just use the Average function, then you will get a value on the total row.

You would still use these measures the same way as before:

Hope that helps.
Kaz.

 

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixHari_example_v4.pbix

16. RE: Rate change calculation DAX

Gold Contributor
Vinu Hari
Posted Sep 28, 2022 04:05 AM | view attached
Hi kaz. Hope all is well.

Just recovering from an injusry, I did played with the PBI and found few issues and the major one is in “associated list list rate” since this is tied up to Bud data, the rate will pick only when a “number” is available in bud data table.

Eg

If F budget is 0 and F actual is 1, then the current formula does not pick the “associated list rates”

I think the formula should be based on AC_config/Type, as this will enable to pick “list rate”

Attached the revised file

——————————
FA
——————————

Attachment(s)

pbixHari_example_v5.pbix