locked
How to calculate weekly minimum and Maximum fore previous 5 years using DAX formulas RRS feed

  • Question

  • I am trying to create a stacked bar and line chart in power-bi showing the weekly minimum and maximum for the same week number of the year for the last 5 years. 

    My current Dax formula is not working as I am rolling the minimum for every day in that year instead of calculating the actual minimum for that week value for the same week in the last five years

    HIS_VALUE min per WeekOfYear = CALCULATE(MIN('ECOBAL v_Americas_working'[HIS_VALUE]), DATESINPERIOD('ECOBAL v_Americas_working'[BK_date],LASTDATE('ECOBAL v_Americas_working'[BK_date]),-365,DAY))

    My data looks like this 

    BK_date BK_ctry_code BK_product BK_flow HIS_VALUE HIS_UNIT
    1/6/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      277 KBD             
    1/13/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      245 KBD             
    1/20/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      270 KBD             
    1/27/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      284 KBD             
    2/3/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      220 KBD             
    2/10/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      254 KBD             
    2/17/2012 USA_P4           GAS_OTHER        P_REFOUTPUT      329 KBD             


    

    Thursday, July 19, 2018 8:33 PM

Answers

  • Hi Santaoct,

    Thanks for your question.

    >>>I am trying to create a stacked bar and line chart in power-bi showing the weekly minimum and maximum for the same week number of the year for the last 5 years.
    In this scenario, you just need to create a date dimension table and mark it as a date table, this date dimension will include Year and week number of year, create relationship with your table 'ECOBAL v_Americas_working' and date dimenison using BK_date and date columns.

    Then you can just create your MAX and MIN measure as below:

    HIS_VALUE min per WeekOfYear = MIN('ECOBAL v_Americas_working'[HIS_VALUE])
    
    HIS_VALUE max per WeekOfYear = MAX('ECOBAL v_Americas_working'[HIS_VALUE])
    


    Then you can just drag year and week number of year to Axis, drag your max and min measure to Values, and use advance filters to filer year and week number of year. See below screenshot:

    According to your description, your problem is more related to Power BI. Since our forum is discussing Power Pivot issue, to solve your question more efficiently, please post your question in Power BI forum: http://community.powerbi.com ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    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, July 20, 2018 1:59 AM

All replies

  • Hi Santaoct,

    Thanks for your question.

    >>>I am trying to create a stacked bar and line chart in power-bi showing the weekly minimum and maximum for the same week number of the year for the last 5 years.
    In this scenario, you just need to create a date dimension table and mark it as a date table, this date dimension will include Year and week number of year, create relationship with your table 'ECOBAL v_Americas_working' and date dimenison using BK_date and date columns.

    Then you can just create your MAX and MIN measure as below:

    HIS_VALUE min per WeekOfYear = MIN('ECOBAL v_Americas_working'[HIS_VALUE])
    
    HIS_VALUE max per WeekOfYear = MAX('ECOBAL v_Americas_working'[HIS_VALUE])
    


    Then you can just drag year and week number of year to Axis, drag your max and min measure to Values, and use advance filters to filer year and week number of year. See below screenshot:

    According to your description, your problem is more related to Power BI. Since our forum is discussing Power Pivot issue, to solve your question more efficiently, please post your question in Power BI forum: http://community.powerbi.com ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    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, July 20, 2018 1:59 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Weekly min/max for 3 years.
    Transformed into PP friendly format with PQ.
    http://www.mediafire.com/file/s303k1maae9ulbu/07_24_18a.xlsx/file
    http://www.mediafire.com/file/otk4u647bp7huv9/07_24_18a.pdf/file

    • Proposed as answer by AV111Editor Sunday, February 24, 2019 9:48 AM
    Wednesday, July 25, 2018 1:46 AM