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

[The8]

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 values are calculated.

Is that possible to have this dynamic measure in Power BI for calculating savings as measure as to change according to date selection.

Link to access file: https://docs.google.com/spreadsheets/d/1OMu3pwWgJry2ahLVjC9ip9ISoMbtWJb_/edit?usp=drive_link&ouid=10...

Thanks in advance!!

[Ashish_Mathur]

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]

Hi @Ashish_Mathur , @v-yifanw-msft 

Thanks for your response.

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

Raw Data:

Material	Date	Quantity	Price
X1	01.01.2023	88000	130262
X2	01.01.2023	66000	133643,4
X3	01.01.2023	84000	283080
X1	01.01.2024	66000	99220
X2	01.01.2024	36000	73198,8
X3	01.01.2024	0	0
X1	01.02.2023	110000	154022
X2	01.02.2023	66000	125947,8
X3	01.02.2023	0	0
X1	01.02.2024	88000	127028
X2	01.02.2024	56660	111166,9
X3	01.02.2024	42000	128520

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]

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

Thanks !

[Ashish_Mathur · Accepted solution]

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

[The8]

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.

[The8]

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]

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

[The8]

Hi @Ashish_Mathur 

Yes I agree, if you have any Patreon account you can share me the details. I would like to fund.

[Ashish_Mathur]

That would not be required.  Thank you.

[v-yifanw-msft]

Hi @The8 ,

Yes, it is indeed possible to implement this functionality in Power BI using DAX to create dynamic metric values in response to slicer selections. You can follow the steps below to create a dynamic metric that calculates savings based on date selection:

1.Make sure you have a table of dates that covers all the dates you are interested in. This table should be labelled as a "Date" table in Power BI. You can learn how to create it here:
Auto date/time guidance in Power BI Desktop - Power BI | Microsoft Learn

2.Ensure that there is a relationship between the date table you create and the table that contains the "cost savings" data based on the date columns.

3.Create a DAX metric that calculates savings based on the selected dates.

Dynamic Savings =
VAR SelectedDate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE ( SUM ( 'Table'[Cost saving] ),
    Filter( 'Table' , 'Table'[Date] = SelectedDate )

4. Add a slicer to the report based on the Date column in the Date table. This slicer will allow the user to select a date and the Dynamic Savings metric will be calculated based on this selection.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.