Forum Discussion

SaraVM's avatar
SaraVM
Regular Visitor
6 years ago
Solved

Compare Same Week Last Year Per Category

Hi all, 

 

I've been trying to work out how to compare values of visits per market for the same week last year but nothing seems to work. 

 

1. I have a Calendar date table on DAX

2. It's linked to my values table as per YearWeek (e.g. 202012)

3. Created measures for volume (visits) per week and market

4. Created measure for volumes (visits) LY using SAMEPERIODLASTYEAR formula. This seems to work if I then run a table per month, quarter or year., but when I do per week, then the values are a lot higher than what they should be. 

 

I've tried many other ways of getting the same week last year's values but none of them work, and especially they seem to fail with the filtering per market. 

 

I saw a similar post with the same question as a reply but no solution. 

I wonder if someone can help. 

 

Thanks a million, 

Sara 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi SaraVM ,

     

    Please try this:

    Total Visits VOL = SUM ( IntMarkets[Total Visits] )
    Total Visits LY = 
VAR ThisYear =
    MAX ( IntMarkets[Year] )
VAR LastYear = ThisYear - 1
RETURN
    CALCULATE ( [Total Visits VOL], IntMarkets[Year] = LastYear )

    For more details, please check the attached PBIX file.

     

     

    Best Regards,

    Icey

     

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

10 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey SaraVM ,

     

    please provide a screenshot from your calendar table and the relationship views, so that it's clear how your calendar and values table are related.

    Please also provide the DAX for the volume (visits) measure.

     

    Regards,

    Tom

    • SaraVM's avatar
      SaraVM
      Regular Visitor
      6 years ago

      Hi  Tom, 

       

      Sorry I'm new to this forum and struggling with uploading images. Is there a secret to that? 

       

      My data table looks like this

      Year

      Week

      YearWeek

      Market

      Total Visits

      2020

      12

      202012

      Europe

      50

       

       

       

      China

      100

       

       

       

      USA

      30

       

      My calendar table is created with the following DAX:

      Date =

      ADDCOLUMNS (

      CALENDAR (DATE(2010,1,1), DATE(2025,12,31)),

      "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

      "Year", YEAR ( [Date] ),

      "Monthnumber", FORMAT ( [Date], "MM" ),

      "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

      "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

      "MonthNameShort", FORMAT ( [Date], "mmm" ),

      "WeekNo", FORMAT(WEEKNUM( [Date], 21),"00"),

      "YearWeek", FORMAT([date], "YYYY") & FORMAT(WEEKNUM( [Date], 21),"00"),

      "Quarter", "Q" & FORMAT ( [Date], "Q" ),

      "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

       

      I link both via YearWeek variable

       

      The measure are calculated as follows

      Total Visits VOL = SUM(IntMarkets[Total Visits])

      Total Visits LY = CALCULATE([Total Visits VOL], SAMEPERIODLASTYEAR('Date'[Date]))

       

      Is this enough info? Any ideas on what I'm missing and how to resolve it?

       

      Thank you!!