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 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=104874748343894866255&rtpof=true&sd=true

Thanks in advance!!



Show and Tell

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

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