@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

------------------------------

Original Message:

Sent: Sep 10, 2022 09:48 AM

From: Vinu Hari

Subject: Rate change calculation DAX

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

Original Message:

Sent: Sep 09, 2022 05:25 PM

From: Kaz Shakir

Subject: Rate change calculation DAX

@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

Original Message:

Sent: Sep 09, 2022 12:47 PM

From: Vinu Hari

Subject: Rate change calculation DAX

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

Original Message:

Sent: Sep 08, 2022 09:08 PM

From: Kaz Shakir

Subject: Rate change calculation DAX

attachment

------------------------------

Kaz Shakir

Sr. Program Manager, Asset Planning

TN

Original Message:

Sent: Sep 08, 2022 07:52 PM

From: Kaz Shakir

Subject: Rate change calculation DAX

@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

Original Message:

Sent: Sep 07, 2022 11:16 PM

From: Vinu Hari

Subject: Rate change calculation DAX

Just a simple one - how to avoid calculating "sum of list rate' at the bottom.

------------------------------

FA

Original Message:

Sent: Sep 07, 2022 11:15 PM

From: Vinu Hari

Subject: Rate change calculation DAX

Dear Kaz, Wonderful, this is working, thanks for the help

------------------------------

FA

Original Message:

Sent: Sep 07, 2022 07:36 PM

From: Kaz Shakir

Subject: Rate change calculation DAX

@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

Original Message:

Sent: Sep 07, 2022 01:02 PM

From: Vinu Hari

Subject: Rate change calculation DAX

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.

------------------------------

FA

Original Message:

Sent: Sep 07, 2022 05:14 AM

From: James Watts

Subject: Rate change calculation DAX

You need to provide details of what the calculation should be though.

------------------------------

James Watts

Business Intelligence Analyst

Original Message:

Sent: Sep 06, 2022 12:05 PM

From: Vinu Hari

Subject: Rate change calculation DAX

Hi, I did not made any DAX for measure, I need one

The attachment is only having simple data

------------------------------

FA

Original Message:

Sent: Sep 06, 2022 02:48 AM

From: James Watts

Subject: Rate change calculation DAX

You need to publish details of the calculations.

------------------------------

James Watts

Business Intelligence Analyst

Original Message:

Sent: Sep 05, 2022 11:44 PM

From: Vinu Hari

Subject: Rate change calculation DAX

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

------------------------------