Week to date figures for last year

[bo_afk]

My data shows sales figures for each day of the current week for this year, plus the same week the previous year. Since the week is not yet finished, we have some blank fields for some days.

Week 46

Day	2019	2018
Sunday	18,667	17,179
Monday	13,931	4,709
Tuesday	11,493	5,893
Wednesday	12,916	6,694
Thursday	17,703	9,619
Friday	-	5,942
Saturday	-	6,587

Is it possible to calculate a 'week-to-date' figure for the previous year, but only for the days we have data for in the current year?

E.g. we only have 2019 data for sunday-thursday, can we calcualte the total for sunday-thursday for 2018 as well?

My data model has the following columns

Year

Week number

Day of the week (in number, starting Sunday)

Day name of the week

Sales figure

Many thanks

afk

[v-jayw-msft]

Hi @bo_afk ,

Please check following steps as below and see if the result achieve your expectation:

1. Create calculated table as slicer:

Table 2 = DISTINCT('Table'[Year])

 2. Create measures:

Measure = 
var valuesprevious = CALCULATETABLE(VALUES('Table'[Day of the week]),FILTER(ALL('Table'),'Table'[Year] = MAX('Table'[Year])+1&&'Table'[Week number] = MAX('Table'[Week number])))
return
IF(MAX('Table'[Day of the week]) in valuesprevious ,MAX('Table'[Sales figure]),"-")

Measure 2 = CALCULATE(SUMX('Table',[Measure]),FILTER('Table','Table'[Year] = SELECTEDVALUE('Table 2'[Year])-1))

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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