Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Rate change calculation DAX

Jump to Best Answer
  • 1.  Rate change calculation DAX

    Gold Contributor
    Posted 22 days ago
      |   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)

    pbix
    GH1-test.pbix   869 KB 1 version


  • 2.  RE: Rate change calculation DAX

    Bronze Contributor
    Posted 22 days ago
    You need to publish details of the calculations.

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 3.  RE: Rate change calculation DAX

    Gold Contributor
    Posted 22 days ago
    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
    Posted 21 days ago
    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
    Posted 21 days ago
    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

    Posted 20 days ago
      |   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)

    pbix
    Hari_example_v1.pbix   882 KB 1 version


  • 7.  RE: Rate change calculation DAX

    Gold Contributor
    Posted 20 days ago
    Dear Kaz, Wonderful, this is working, thanks for the help

    ------------------------------
    FA
    ------------------------------



  • 8.  RE: Rate change calculation DAX

    Gold Contributor
    Posted 20 days ago
    Just a simple one - how to avoid calculating "sum of list rate' at the bottom.

    ------------------------------
    FA
    ------------------------------



  • 9.  RE: Rate change calculation DAX

    Posted 19 days ago
    @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

    Posted 19 days ago
      |   view attached
    attachment

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

    Attachment(s)

    pbix
    Hari_example_v2.pbix   883 KB 1 version


  • 11.  RE: Rate change calculation DAX

    Gold Contributor
    Posted 19 days ago

    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

    Posted 18 days ago
    @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
    Posted 18 days ago
      |   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)

    pbix
    GHA test.pbix   880 KB 1 version


  • 14.  RE: Rate change calculation DAX
    Best Answer

    Posted 17 days ago
    Edited by Vinu Hari 10 hours ago
      |   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)

    pbix
    Hari_example_v3.pbix   886 KB 1 version


  • 15.  RE: Rate change calculation DAX

    Posted 16 days ago
      |   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)

    pbix
    Hari_example_v4.pbix   886 KB 1 version


  • 16.  RE: Rate change calculation DAX

    Gold Contributor
    Posted 11 hours ago
      |   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)

    pbix
    Hari_example_v5.pbix   916 KB 1 version