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

• 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

• Hi Santaoct,

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

>>>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 Sunday, February 24, 2019 9:48 AM
Wednesday, July 25, 2018 1:46 AM