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.