Forum Discussion

The8's avatar
The8
Helper II
2 years ago
Solved

Dynamic Measure as per selection (In my case Date selection)

Hi, I have excel file as in link. In this excel file I have Column "Cost saving" which will change according to date selection in cell "B1" .Based on this date selection  "Savings" column value...
Show and Tell
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Access denied message.  In another tab of the Excel file, clearly show the expected result with formulas.  I will try to convert those formulas into PowerBI measures.

  • The8's avatar
    The8
    Helper II
    2 years ago

    Hi Ashish_Mathur , Anonymous 

    Thanks for your response.

    I will try to provide data clearly if you coudn't able to access the file.

    Raw Data:

    Material DateQuantityPrice
    X101.01.202388000130262
    X201.01.202366000133643,4
    X301.01.202384000283080
    X101.01.20246600099220
    X201.01.20243600073198,8
    X301.01.202400
    X101.02.2023110000154022
    X201.02.202366000125947,8
    X301.02.202300
    X101.02.202488000127028
    X201.02.202456660111166,9
    X301.02.202442000128520


    Net Price:
    Cell D7=IFERROR(H7/L7;C7)

    Cost Saving Cell:
    Cell B5 ==INDEX($C5:$F5;1;MATCH($B$1;$C$4:$F$4;0))


    Savings:
    Cell O5 = =IFERROR(M5*(E5-$B5);"")

     

    To be more clear,
    As I change date in cell B1, values in column B (Cost saving ) will change, accordingly values in "saving" column will change.

    For example:
    Selected Date : 01.01.2023


    Selected Date 01.02.2023


    Now in excel we see values of column O depends on Column B and this column B values are changing according to date selection in cell "B1".  In Power BI I am trying to have a measure for Saving column.

    • The8's avatar
      The8
      Helper II
      2 years ago

      Hi Ashish_Mathur 
       
      Is that possible to achieve above in power BI?

      Thanks !

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        This is what i have been able to come up with.  PBi file attached.