Comparison between last week's status and this week

DAX DAX Calculations
1

Hello,

I am trying to achieve 2 outcomes:

1- Compare between last week’s (LW) status and this week (CW) , to help me check on the progress.

Attached is the StatusTest.pbix (60.7 KB) file with a small dummy data.

Outcome should be like the below :
image

2- based on the above, I want to check if CW status = LW Status, then 1, else 0. This column will be used in other calculations.

I appreciate that the model is too small, but thought of putting something quick.

Thanks
H

3

Hello @Hesham I am looking at your date table and I would suggest loading Melissa’s DateTable from the forum first.

The date table includes IsPreviousWeek and IsCurrentWeek. Then you can write either a dax measure or filter using one or the other. I have downloaded you pbix file and will take a look.

Extended Date Table (Power Query M function) - M Code Showcase - Enterprise DNA Forum

4

@Hesham

And you need a relationship between your Data (Date) and your Date Table as a One to Many

5

@Hesham

After you get your Date Table set up, add a column as follows:

IsCurrentWeek =
var vCurrentWeek = CALCULATE(MAX (‘DATE’[WEEK & YEAR]),
FILTER(ALL(‘DATE’), ‘Date’[Date] = TODAY()))
RETURN
IF(‘Date’[Week & Year] = vCurrentWeek, “Y”, “N”)

Then add,

IsPreviousWeek=

VAR vIsPreviousWeek = TODAY () - ‘Date’[Date]

RETURN

IF (vIsPreviousWeek > 4 && vIsPreviousWeek < 12, “Y”, “N”)

The calculated columns are based on TODAY(). So in the PBIX it’ll say no for both previous week and current week. But if you add the full year in and as you continue with data then you will see the Yes and No appear.

Hope this helps.

Paul

StatusTest.pbix (133.0 KB)

2 Likes
6

Hi @Paul.Gerber ,

Thanks for looking into my question. Sorry about the rough pbix file attached (date & relationship), it was a quick one.

Thanks again for providing your solution, it resolve part 2 of the requirement (Y or N). But I think all the results are showing “N”.

As for the main question, last night I found a solution to get me the column I need, and here is the solution:

VAR _weekNo = MAX('Calendar'[Week number]) - 1
RETURN
CALCULATE(MIN(Data[Status]), 
FILTER(ALL(Data), Data[Emp ID] = MAX(Data[Emp ID]) && Data[Category] = MAX(Data[Category])) , 
FILTER(ALL('Calendar'),'Calendar'[Week number] = _weekNo))

Here is the solution file.
StatusFInal.pbix (46.1 KB)

Have a great day.

Happy for this one to be closed.
H

1 Like
7

@Hesham that’s great. Glad I could help. Also happy to hear you did some digging and found a answer. Thanks again. Just check the box for the post that provided a solution for you to close the thread.

Paul