Forum Discussion

Devtr's avatar
Devtr
Icon for Helper III rankHelper III
3 years ago
Solved

Weekly sales YOY comparison per day

Hello everyone,

I am trying to compare weekly sales this year vs the previous year with same week number.

The matrix needs to have columns that show this year's sales Sunday, Monday and so on.

The bottom rows show the previous year data (based on the offset value from the slicer).

 SundayMondayTuesdayWednesdayThursdayFridaySaturday
Qty       
Revenue       
Qty Last year       
Revenue Last year       

 

The SalesData is related to the Calendar, which has dates, year, the first date of the week, and the week number.

The matrix has the Year, First date of the week, and offset previous year filters.

 

 

 

var sy_year = VALUE(SELECTEDVALUE('Calendar'[sales_date].[Year]))
var year_offset = VALUE(SELECTEDVALUE('Tbl_Year_Offset'[Year_Offset]))
var py_year = sy_year - year_offset

var sy_start_week = SELECTEDVALUE('Calendar'[start_WEEK_date])
var sy_week_number = CALCULATE(VALUES('Calendar'[sales_week_number]), FILTER(ALL('Calendar'[start_WEEK_date]),[start_WEEK_date]=sy_start_week))

return
CALCULATE(VALUES('SalesData'[Qty]),FILTER(all('SalesData'[Sales_year],'SalesData'[sales_week_number]),'SalesData'[Sales_year]=py_year && 'SalesData'[sales_week_number]=sy_week_number))

 

 

 

I am not fluent in DAX. Please help.

amitchandak 

Greg_Deckler 

FreemanZ 

Thanks.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Devtr , same weekday last year is 364 days behind

     

    week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

     

     

    or

     

    This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
    Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

     

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • Devtr's avatar
      Devtr
      Icon for Helper III rankHelper III
      3 years ago

      Hi amitchandak 

      Please check out the images.

      I have shared a link for the pbix as well.

      The user has the ability to select the first date of the week and the year as well.

      There is a slicer to offset the previous year as well. So subtracting 364 days will not work.

      I tried the measure. But it shows 99 for all the days.

      Prev_year_Qty = 
var sy_year = VALUE(SELECTEDVALUE('Calendar'[sales_year]))
var year_offset = VALUE(SELECTEDVALUE(Tbl_Offset_Year[Value]))
var py_year = sy_year - year_offset

var sy_start_week = SELECTEDVALUE('Calendar'[sales_first_week_date])
var sy_week_number = CALCULATE(VALUES('Calendar'[sales_week_number]), FILTER(ALL('Calendar'[sales_first_week_date]),'Calendar'[sales_first_week_date]=sy_start_week))

return
CALCULATE(SUM('Sales'[qty_sold]),FILTER(all('Calendar'),'Calendar'[sales_year]=py_year && 'Calendar'[sales_week_number]=sy_week_number))

      The relationship and model image:

       

      The file can be downloaded here.

       

      Thanks again.