Get last date within a table convert to status.

[Patronskive]

I need a little help. 

I have a table a table with multiple registrations on each name monthly.
I would like to have a table based on the first table or using a measure where i can present the following:

For each name based on the date of registration.
1. Have they registered that month (OK)
2. Have they registered last month but not this month (PENDING)
3. Have they not registered the last two months (MISSING)
4. If they have not registered for the last 3 months (then don't show anything)
5. If a person that has returned after more than 3 months (RETURNED)

It is a large dateset, so I want to present it with with distinct count per status (OK, PENDING, MISSING, RETURNED) for each month.
Date Format here is (DD.MM.YYYY)

Name IN Location
Peter 01.01.2022 Home
Peter 01.02.2022 Home
Peter 01.03.2022 Home
Peter 01.04.2022 Home
Peter 01.01.2022 Work
Peter 01.02.2022 Work
Peter 01.03.2022 Work
Peter 01.04.2022 Work

Peter 01.05.2022 Work

Peter 01.06.2022 Work

Peter 01.07.2022 Work
Paul 01.01.2022 Home
Paul 01.02.2022 Work
Paul 01.07.2022 Home

Output:
Name Date Status
Peter 01.01.2022 OK
Peter 01.02.2022 OK
Peter 01.03.2022 OK
Peter 01.04.2022 OK
Peter 01.05.2022 OK

Peter 01.06.2022 OK

Peter 01.07.2022 OK
Paul 01.01.2022 OK
Paul 01.02.2022 OK
Paul 01.03.2022 PENDING
Paul 01.04.2022 MISSING
Paul 01.07.2022 RETURNED

[amitchandak · Accepted solution]

@Patronskive , With help from date table, you have to create few measures like join you date with date tbale and use month year from there

assume you count measure cnt

rolling 3 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max, -3)+1
BLANK())
return
CALCULATE([cnt] ,DATESBETWEEN('Date'[Date],_min,_max))

rolling 3 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max, -2)+1
BLANK())
return
CALCULATE([cnt] ,DATESBETWEEN('Date'[Date],_min,_max))

rolling 3 till last month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _min = eomonth(_max, -4)+1
BLANK())
return
CALCULATE([cnt] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([cnt] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([cnt] ,DATESBETWEEN('Date'[Date],_min,_max))

before This Month =
var _max = eomonth(eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0),-1)
var _min = minx(all('Date'), Date[Date])
return
CALCULATE([cnt] ,DATESBETWEEN('Date'[Date],_min,_max))

Something is present if measure is not blank

not present means blank

You need condition like

Switch(True(),

isblank([Before this month]) && not(isblank([This Month])) , "Ok",

not(isblank([Last Month])) && isblank([This Month]), "Pedning",

// Add other conditions

)

refer

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

[Patronskive]

Thanks for this, amitchanak. I will use this approach.