Forum Discussion

DLimerick's avatar
DLimerick
Regular Visitor
8 years ago

Comparing data by week and the same week number from a previous year.

I have a table in PBI with date annd have extracted the year and week number for each date into separate columns.

I can graph my data which counts all created records by week and I have a slicer that will allow me to select a year- all good. Columns are Created_date, Week No. and Year.

 

However I need to be able to plot the previous years data for the same weekly periods from the prior year on the same graph and this is where I am running into trouble. I am new to PBI and a little lost. I gather I need to create new measures?

 

 

so For example if I select 2018 for created records it shows me by week all 2018 records and also all 2017 records for the same week number.

 

Any help or guidance would be greatly appreciated.

9 Replies

  • DLimerick's avatar
    DLimerick
    Regular Visitor
    8 years ago

    HI All,

     

    I am trying to graph created reports this year by week to created reports for the same weekly periods last yeat. My table has columns Created_Date, Week No., Year. I can see this years breakout but not sure how to get the totals for the same week numbers last year. I assume this is done through creating measures. I have a slicer that can break this out by year. If i choose 2018 i want o be able to see 2018 weekly numbersa and also the weekly totals for 2017.

     

    I'm new to Power Bi and would greatly appreciate any help or guidance.

     

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi DLimerick

    Measure here can get the same value for the same week last year

    Measure = CALCULATE(MAX([Created_date]),SAMEPERIODLASTYEAR(Sheet1[Created_date]))

     

    Best Regards

    Maggie

    • DLimerick's avatar
      DLimerick
      Regular Visitor
      8 years ago

      Thank you Maggie,

       

      However what I am looking to do is to determine the week number of each date from the previous year. for example 7th Januuary 2017 might be week number 1 in 2017 and 7th January 2018 might be week number 2. I want to compare all dates in week one last year with all dates in week 1 this year. I hope that makes sense.

       

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi DLimerick

        Yes, after nodifying this formula with the following one , it can make sense for your example.

        Measure = CALCULATE(MAX([Week No.]),SAMEPERIODLASTYEAR(Sheet1[Created_date]))

         

        Best Regards

        Maggie