Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
The8
Helper II
Helper II

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

The8_0-1712759728656.png

 

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



1 ACCEPTED SOLUTION

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

Ashish_Mathur_0-1713248324202.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)

The8_1-1712823934095.png

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

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

The8_4-1712824641372.png

 

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

The8_5-1712824735644.png


Selected Date 01.02.2023

The8_6-1712824775919.png


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 
 
Is that possible to achieve above in power BI?

Thanks !

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

Ashish_Mathur_0-1713248324202.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 👇 :

The8_2-1714379828645.png

 

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 
The8_1-1714379670244.png

 

The8_0-1714379644392.png

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

The8_6-1714380140025.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.