Prior month metrics beast mode

Khan

Hi, 

I have a pivot table card which displays Last month projects, Projects 2 Months ago, Delta Projects.

 

Below are the Beast mode calculations:

Last Month Projects

COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-1 THEN `ProjectNo` END)

Projects 2 Months ago

COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-2 THEN ` ProjectNo` END)

Delta Projects

(COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-1 THEN `ProjectNo` END))-

(COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-2 THEN `ProjectNo` END))

 

Since we entered into a new year the pivot table shows 0 values for the above fields as the beast mode has YEAR(`Date`) = YEAR(CURRENT_DATE), if I remove YEAR(`Date`) = YEAR(CURRENT_DATE), it will select the years right from the beginning regardless of previous year. I want the card to choose previous year(2020), last month even when we are in current year 2021.

Please advise.

Thanks!

jaeW_at_Onyx

@Khan_Naziya  ? i feel like i've suggested to you multiple times to build a date dimension table... you're not gonna love this answer ?  build a date dimension table.

 

What you need is a YearMonth_Lag column that calculates the number of month's lag (0,1,2,3,4,5) from the current date.  You calculate that with a Row_Num() Over ( Partition By YearMonth_Num OrderBy End_Of_Month desc)

 

make sure your window function updates daily.

 

Then you could simplify your beast mode to 

 

CASE WHEN YearMonth_Lag = 0  -- current month

YearMonth_Lag = 1 = prev month

YearMonth_Lag = 12 = one year ago.

 

cc @GrantSmith