Forum Discussion

TomBurke's avatar
TomBurke
Regular Visitor
6 years ago

Weekly Comparison vs Previous Year into KPI Visual

Hello there community,

 

I'm here today seeking your help with time inteligence and creating a KPI Visual with the results.
Currently, I have two KPI's, one for Monthly Profit MTD and Weekly Profit WTD.

Monthly Profit MTD is a KPI and it works flawlessly. 
I have a standard Total Profit Measure and a Previous Month Profit Measure -1 Year.
Dax Code: Previous Month Profit = CALCULATE([Total Profit] , SAMEPERIODLASTYEAR(Rolling_Calendar[Date]))

From what I can tell this is correct, and I have spotchecked it myself various times.

Now for what I am struggling with....
It seems that Power Bi does not have the in-built inteligence for a Weekly Statistic just like this.

I am looking for any kind of guideance or ideas on how I can extract that weekly profit vs Last Year and place it into my KPI to show Week on Week performance vs Last Year.

(I have tried all the obvious, but I feel I am just not seeing something!)


Thank you community!

 

Need Help

8 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    SAMEPERIODLASTYEAR, PARALLELPERIOD and DATEADD can all do similar things but with slightly different levels of granularity/detail and behaviour when drilling down to week and month. Try DATEADD and PARALLELPERIOD and experiment with both in your table side by side to see the differences and which one you prefer. 

    NOTE: PARALLELPERIOD will behave differently if you put in -12 MONTH than if you put in -1 YEAR. 

     

    Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

     

    If you found this post helpful, please give Kudos.

    I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

    https://sites.google.com/site/allisonkennedycv

    • TomBurke's avatar
      TomBurke
      Regular Visitor
      6 years ago

      Hi Allison,

       

      Thanks very much for your reply.

      I had previosuly tried all 3 versions, but I went back and it seems that DATEADD works fine.
      However, I am getting fictisious results for Week 18 & 19.
      (Unable to post images due to Data Protection).

       

      I have been trouble-shooting in my data and have explored it in Excel and the figures shown using DateAdd are incorrect.

      Is there anything you can suggest that might cause an issue with 2 of 19 results?
      Seems very strange.


      Many thanks,

       

       

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        6 years ago

        Hi TomBurke ,

        In essence, DATEADD expects a table column of dates as first parameter as most of Time Intelligence functions, which are designed to work with Date/Calendar tables (Date dimensions).

        For example, you have this table called MyDates

        If you use DATEADD to create a calculated column called 4MonthsAfter:

         

        4MonthsAfter = DATEADD(MyDates[Date],4,MONTH)

         

        It returns this:

        As you can see the column 4MonthsAfter for the date 24/08/2016 value is blank since the column doesn't contain 24/12/2016 value, the same happens for 24/01/2017 because there is no 24/05/2017 value. But 4 months after 24/09/2016 is computed since 24/01/2017 is present in the column.

         

        In addition, you can refer this article about difference between Dateadd, ParallelPeriod and SamePeriodLastYear:

        DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question 

         

        Best Regards,
        Yingjie Li

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.