Forum Discussion

Pra2010Gha's avatar
Pra2010Gha
Helper I
9 months ago
Solved

Dax command to get the values based on paramter date

Hi I want  a dax command to get the values highlighted based on transaction date parameter.

Say a customer who did a transaction on 18th october and comes back to return those products  on 30 October.

I want the dax to get the date price change that has happened before the transaction date .SO that I can return the amount he paid for the products when the price changed happened and not to over pay him

In this case I want to return the amount 3.5 +6 = $ 9.5 and not 4+7 = $11 (this would be the price on 30th October)

  • v-hashadapu's avatar
    v-hashadapu
    9 months ago

    Hi Pra2010Gha , Thanks for reaching out to the Microsoft fabric community forum.

     

    I have created a new .PBIX file based on your requirements. Instead of using field parameters, I used a slicer for the date column, as field parameters are not the most suitable option here.
     

    On page 1 of the report, you can use the slicer—set it to 18th Oct to get the desired output, which is 3.5 + 6 = $9.5. On page 2, I added a table with a DAX measure to show the price of the fruits on the transaction date. You can use whichever option works best for you. 

     

     

    Please refer to the attached .pbix file for more details and let me know if you have any further questions.
    Thank you.

10 Replies

  • parry2k's avatar
    parry2k
    Super User
    9 months ago

    Pra2010Gha do you have a separate table for transaction date selection? If yes, does it have a relationship with the price change table? 

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    9 months ago

    Hi Pra2010Gha , Thank you for reaching out to the Microsoft Fabric Community Forum.

     

    I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

     

    Thank you for being part of the Microsoft Fabric Community!

    • Pra2010Gha's avatar
      Pra2010Gha
      Helper I
      9 months ago

      Hey Thank you for your reply.It looks like the transaction Date(18 oct) is harcoded in your logic...Will it work if I pass a date parameter there?

       

      • Pra2010Gha's avatar
        Pra2010Gha
        Helper I
        9 months ago

        Also is there a way that it will only filter out the yellow rows and not all the rows? based on the transaction date (parameter date input)?

         

    • Pra2010Gha's avatar
      Pra2010Gha
      Helper I
      9 months ago

      Can some one please add a date parameter to the above pbix file.I tried adding and its not working properlyThanks

      • v-hashadapu's avatar
        v-hashadapu
        Community Support
        9 months ago

        Hi Pra2010Gha , Thanks for reaching out to the Microsoft fabric community forum.

         

        I have created a new .PBIX file based on your requirements. Instead of using field parameters, I used a slicer for the date column, as field parameters are not the most suitable option here.
         

        On page 1 of the report, you can use the slicer—set it to 18th Oct to get the desired output, which is 3.5 + 6 = $9.5. On page 2, I added a table with a DAX measure to show the price of the fruits on the transaction date. You can use whichever option works best for you. 

         

         

        Please refer to the attached .pbix file for more details and let me know if you have any further questions.
        Thank you.

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    9 months ago

    HI Pra2010Gha ,

     

    try below measure:

    Return Amount = 
VAR TransactionDate = [transaction date]  -- Your parameter date (10/18/2025)
VAR CurrentProduct = SELECTEDVALUE('YourTable'[product])

VAR CorrectPrice = 
    CALCULATE(
        MAX('YourTable'[base price]),
        FILTER(
            ALL('YourTable'),
            'YourTable'[product] = CurrentProduct &&
            'YourTable'[date price change] <= TransactionDate
        )
    )

RETURN
CorrectPrice
    Total Return Amount = 
VAR TransactionDate = [transaction date]  -- 10/18/2025

RETURN
SUMX(
    VALUES('YourTable'[product]),
    VAR CurrentProduct = 'YourTable'[product]
    VAR CorrectPrice = 
        CALCULATE(
            MAX('YourTable'[base price]),
            FILTER(
                ALL('YourTable'),
                'YourTable'[product] = CurrentProduct &&
                'YourTable'[date price change] <= TransactionDate
            )
        )
    RETURN CorrectPrice
)

    Create one more  measure to get transaction date as input from slicer/parameter and use it in above measures

     

    Thanks and Regards,

    Praful

     

     

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    9 months ago

    Hi Pra2010Gha , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    9 months ago

    Hi Pra2010Gha , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.