locked
Power BI: Last 13 Weeks LINE chart. RRS feed

  • Question

  • I have a requirement to display <g class="gr_ gr_104 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins replaceWithoutSep gr-progress" data-gr-id="104" id="104">last</g> 13 weeks data for 3 individual years from <g class="gr_ gr_103 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins replaceWithoutSep" data-gr-id="103" id="103">current</g> year. i.e. 2017, 2018 and 2019.

    I want to show data as <g class="gr_ gr_542 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins replaceWithoutSep" data-gr-id="542" id="542">following</g> image.

    https://social.msdn.microsoft.com/Forums/getfile/1404174

    I tried with following measure.

    last 13 weeks from current week =
    var today=TODAY()
    var currentweek=WEEKNUM(today,1)
    return IF(MAX([weeknum])<=currentweek&&MAX([weeknum])>currentweek-13,1,0)

    This gave me data of only the last 8 weeks as that's how much weeks we are in 2019. 

    https://social.msdn.microsoft.com/Forums/getfile/1404176

    Thanks for the help.

    Monday, February 18, 2019 11:48 AM

Answers

All replies

  • So the problem is that your current week calculates as a number between 1 and 53.

    Currently its week 8 so your if condition becomes:

    IF( MAX([weeknum])<= 8 && MAX([weeknum])> -5 ,1,0)

    So the lower condition is > -5 which is not the correct logic.

    A different approach would be to calculate a range of dates using the DATESBETWEEN function

    eg.

    last 13 weeks from current week v2 =
    var _today = TODAY()
    var _13weeksago = _today - (7 * 13)
    var last13weeks = DATESBETWEEN('date'[date],_13weeksago, _today)
    return if(max('date'[date]) in last13weeks,1)


    http://darren.gosbell.com - please mark correct answers

    Tuesday, February 19, 2019 4:31 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Totals for each of the last previous 13 week from today,
    and same period for 2 previous years.
    All calculations done in PQ with M. Chart with PP.
    http://www.mediafire.com/file/qggpa5sjms7w1fh/02_18_19.xlsx/file
    http://www.mediafire.com/file/wuep6empk97xgdm/02_18_19.pdf/file

    Tuesday, February 19, 2019 5:19 AM
  • Hey @Herbert,

    This is exactly the type of chart I want. Could you please tell me how I can achieve this in Power <g class="gr_ gr_178 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" data-gr-id="178" id="178">BI ?</g>

    Thanks very much.
    Wednesday, February 20, 2019 6:14 AM
  • Hey @Darren, 

    I tried your measure but It did not work. I could not get any data with the <g class="gr_ gr_153 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="153" id="153">measure</g> value checked as 1. 
    https://social.msdn.microsoft.com/Forums/getfile/1405065
    Wednesday, February 20, 2019 6:19 AM
  • Hey @Darren, 

    I tried your measure but It did not work. I could not get any data with the measure value checked as 1. 

    I'm not sure why this would not be working for you. It works fine on my test model

    You can download my test Power BI report from my onedrive if you want to have a look at it


    http://darren.gosbell.com - please mark correct answers

    Wednesday, February 20, 2019 6:55 AM
  • Hey Darren,

    Whenever I put the measure in the filter or select the measure there is no data displayed. Could you please help me figure this out?

    Attached images of with and without the measure 

    https://social.msdn.microsoft.com/Forums/getfile/1405228  -- without measure 

    https://social.msdn.microsoft.com/Forums/getfile/1405229  -- with <g class="gr_ gr_66 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="66" id="66">measure</g> 

    Also, If we get this measure to work, Will I be able to get the 3 years data for 13 weeks in the bar chart?

    Thanks for all your help on my question.

    Wednesday, February 20, 2019 10:50 AM
  • Whenever I put the measure in the filter or select the measure there is no data displayed. Could you please help me figure this out?

    The way this measure is defined it does not make sense to use it in a filter as this measure only returns a value based on the current context of the date table. I've just been trying to fix your initial expression, but it sounds like you want something different.

    Also, If we get this measure to work, Will I be able to get the 3 years data for 13 weeks in the bar chart?

    I don't really understand what you mean by "get 3 years for 13 weeks" - do you actually want to calculate a rolling 13 week sum or average or something like that. As that is not what you have in your initial expression, it is simply returning a 1 for any week in the last 13 weeks based off the current system clock.

    If Herbert's file is producing what you want why don't you use that? I have not looked at those files, but it should be easy to copy that into Power BI as Excel is just using an older version of the same data engine that Power BI uses.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, February 20, 2019 11:30 AM
  • Hey Darren,

    Sorry If the measure I posted created confusion in my requirement. I actually need to show the chart as Herbert posted. I am not sure how to achieve what he has done in Power BI.

    I need 13 weeks data for 2017,2018,2019 week-by-week in a line chart.

    Thanks for your help. 

    Thursday, February 21, 2019 4:47 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Totals for each of the last previous 13 week from today,
    and same period for 2 previous years.
    All calculations done in PQ with M. Chart with PP.
    http://www.mediafire.com/file/qggpa5sjms7w1fh/02_18_19.xlsx/file
    http://www.mediafire.com/file/wuep6empk97xgdm/02_18_19.pdf/file

    Hey @Herbert, 

    Would you mind helping me to achieve the chart in power <g class="gr_ gr_93 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" data-gr-id="93" id="93">bi ?</g>

    thanks for your help.

    Regards,
    Success

    Wednesday, February 27, 2019 6:23 AM
  • As Darren Gosbell noted, Power BI and Excel 365 use the same PowerPivot engine.
    Besides, my solution does not use any DAX Time Intelligence Functions,
    so there is no need to copy DAX stuff to Power BI.
    Neither is PivotChart or even regular PivotTable needed.
    What is needed is Power Query,
    which is in every version of Excel 2010 to 2019, and in Power BI.

    Here is a solution for those who prefer actual yearly week numbers:
    http://www.mediafire.com/file/c8bwq9tk64d82cr/02_18_19a.xlsx/file
    http://www.mediafire.com/file/3saaqi1s18pfhs5/02_18_19a.pdf/file
    Darren:
    What tools are needed to translate the OP's posts?

    Wednesday, February 27, 2019 4:34 PM
  • Hey Herbert,

    Would you kindly provide the M code you used for the calculation? I am not that familiar with M code so not sure how you achieved the chart in excel. 

    I read a couple of blogs which mentioned that I can use the same M code in power bi to get the desired result as well.

    Regards,
    Success

    Sunday, April 21, 2019 10:18 AM
  • The M code is in my shared files.
    How to access the code and adapt it to Power BI is covered in this book:
    "Collect, Combine, and Transform Data using Power Query in Excel and Power BI" by Gil Raviv.
    A daunting three month task.

    Sunday, April 21, 2019 2:55 PM
  • Thank you sir, I will try to look at the book once. 

    Regards,

    Success

    Monday, April 22, 2019 8:40 AM