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
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.

     

    • The8's avatar
      The8
      Helper II
      2 years ago

      Hi Ashish_Mathur 

      I tried you solution with relal time data, your logic and measures fits very well.

      Thanks for your time and response.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        You are welcome.  This was a tough one to solve.

    • The8's avatar
      The8
      Helper II
      2 years ago

      Hi Ashish_Mathur ,

      The reason for asking below question is Dulpicate date rows between Earliest and Latest date in a table because when I change remove rows where Quantity and Price columns are Zero then I coudn't able to get the expected result as below

      Before changing data 👇 :

       

      After removing rows (6 & 9) when Quantity equals to Zero. I am missing value for Material X3 for the month of February 2024 as in excel screen shot below 

       



      After removing rows 6 & 9, I am missing Material X3 for the month of February 2024 as below 

      In my actual data I don't have data when quantity is Zero, so I just wanted to duplicate the values with Zero between earliest and Latest available dates. 
      By the way, thanks for your response.