Forum Discussion

aavilap's avatar
aavilap
Helper I
8 years ago

Last 3 Week Sales returning blank when current week is empty

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

 

 

 

 

 

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support

    Hi aavilap

    Try this formula

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

    Best Regards

    Maggie

    • aavilap's avatar
      aavilap
      Helper I

      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.