Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!!
Solved! Go to Solution.
This is what i have been able to come up with. PBi file attached.
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.
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 | 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.
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.
Hi @Ashish_Mathur
I tried you solution with relal time data, your logic and measures fits very well.
Thanks for your time and response.
You are welcome. This was a tough one to solve.
Hi @Ashish_Mathur
Yes I agree, if you have any Patreon account you can share me the details. I would like to fund.
That would not be required. Thank you.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |