Last 3 Week Sales returning blank when current week is empty

[aavilap]

Hi all! Hope you can help with this, I have a measure to add up sales of last 3 weeks, the measure is :

_P3 = CALCULATE(SUM(Dashboard_Weekly[Value]),DATESBETWEEN(Dashboard_Weekly[Week],[_L3W],[_ThisWK]))

Where _ThisWK = SELECTEDVALUE(Dashboard_Weekly[Week]) and _L3W = [_ThisWK]-14

So far the result I am getting is shown in the table below, as you'll see the measure works fine when there are sales for current week (22/07/2018, 15/07/2018, etc..) however it is not working properly if the current week has no sales, for example for week 29/07/2018 the result should be = 12 (8 from 22/07 plus 4 from 15/07). 

I also tried a different formula :

L3 = CALCULATE(SUM(Dashboard_Weekly[Value]),DATESINPERIOD(Dashboard_Weekly[Week],LASTDATE(Dashboard_Weekly[Week]),-21,DAY),ALLSELECTED(Dashboard_Weekly[Week]))

but with that one I just get blanks where there are no sales on current week.

Appreciate any help you can provide, I can share my PBIX if necessary.

Thanks in advance!

Ale

[v-juanli-msft]

Hi @aavilap

Try this formula

Measure = CALCULATE(SUM(Sheet7[current]),DATESINPERIOD(Sheet7[date],MAX(Sheet7[date]),-21,DAY),ALL(Sheet7))

Best Regards

Maggie

[aavilap]

Thanks @v-juanli-msft! I have managed to solve it by creating an independent dates table and creating the relation between my Data table and this new dates table.