Prior Week Calculation

Power BI
1

Hi all,

I have followed this video from Enterprise DNA, https://youtu.be/jclWnA7pEvY , on how to compute prior week sales. But for some reason my output is not correct. Below is the DAX formula and the output and I have attached a WIP file. Thank you for your help.

image

image

WIP File.pbix (11.0 MB)

2

Hello @ysherriff . I have done the same thing at my current job to show the number of active oil rigs in the country but needed to show the previous week only. See my DAX code below. Then in your date table make sure you have a column called IsLastWeek (Y or N). Use SumX and not CountX

image

image
image397×656 35.8 KB

3

Thanks Phil but I have to use CountX because my fact table are non-numerical. They are texts.

I will update the formula and see what the results are and get back to you.

4

image

Is this not what you are needing?

Paul

1 Like
6

Phil, What is the formula to create an IsLastWeek Column?

Thank you

7

I get this error message when I change it from Countx to SumX.

image

My fact table are text based.

Thanks

8

strange. I used the file you attached to get the results I showed.

IsLastWeek =
var vLastWeek = CALCULATE(MAX (‘DATE’[WEEK & YEAR]),
FILTER(ALL(‘DATE’), ‘Date’[Date] = TODAY()-6))

RETURN

IF(‘Date’[Week & Year] = vLastWeek, “Y”, “N”)

1 Like
9

Can you upload the file Paul? Let me check something

Thank you

1 Like
10

WIP File mod.pbix (11.1 MB)

11

Thanks Paul

1 Like
12

I must have a space or something in my formula but it works. Thanks for your help.

1 Like
13

@ysherriff Is it working for you now? There is a great date query here on the forum I would recommend.

1 Like
14

Yes and thank you. Can you point me to the resource Paul?

1 Like
15

@ysherriff