locked
Display Product wise Historical Minimum Rate against Date RRS feed

  • Question

  • Hi There,

    I have a below "Data" Table:

    Date Product Rate
    01-Jan-16 A 15
    02-Jan-16 A 13
    03-Jan-16 A 9
    04-Jan-16 A 13
    05-Jan-16 A 13
    01-Jan-16 B 15
    02-Jan-16 B 9
    03-Jan-16 B 14
    04-Jan-16 B 9
    05-Jan-16 B 6
    01-Jan-16 C 6
    02-Jan-16 C 14
    03-Jan-16 C 10
    04-Jan-16 C 6
    05-Jan-16 C 8


    is it possible to create a DAX Function to display the Minimum Rate for each Product for all given date? Result Table Shown bellow:

    Product A
    Date Rate  Min of Rate
    01-Jan-16 15.00 9.00
    02-Jan-16 13.00 9.00
    03-Jan-16 9.00 9.00
    04-Jan-16 13.00 9.00
    05-Jan-16 13.00 9.00

    Thanks for your help.

    Regards,

    Sami


    Sami



    • Edited by samihuq Friday, December 30, 2016 2:20 AM
    Friday, December 30, 2016 2:18 AM

Answers

  • Hi samihuq,

    You’d better create a measure to calculate the minimum rate for each product using the following formula. 

    min of rate:=CALCULATE(MIN(Table3[Rate]),ALLEXCEPT(Table3,Table3[Product]))

    Then create a pivot table, select the category and rate field as Row level, select the measure as value level like the first screenshot. You will get the expected result like the second picture.

       

    If you have any other issue, please feel free to ask.      

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 30, 2016 7:12 AM