Return Status at the time of a billing month

[kuraitengai]

Trying to work through this myself but getting nowhere.

I have one table, PartnerStatus

PartnerName	Status	DateMovedToStatus
Partner A	Bronze	7/1/2009
Partner A	Silver	7/1/2019
Partner B	Bronze	7/1/2009
Partner B	Silver	7/1/2014
Partner B	Gold	7/1/2016

Then I have a billings table, Billings

PartnerName	CommRun	NetBilled
Partner A	9/1/2015	500
Partner A	10/1/2015	400
Partner A	11/1/2015	600
Partner B	9/1/2015	800
Partner B	10/1/2015	900
Partner B	11/1/2015	700

I need to figure out how to do a DAX calculated column that would take the DateMovedToStatus and see what Status the partner was in at that time.

I am trying to do it on a full year basis. so if they moved to Silver on 7/1/2015, then starting with the 1/1/2016 CommRun they status would be Silver

So the result table would be something like this

PartnerName	CommRun	NetBilled	Status
Partner A	12/1/2009	400	No Status
Partner A	1/1/2010	500	Bronze
Partner A	...	...	...
Partner A	12/1/2019	600	Bronze
Partner A	1/1/2020	800	Silver
Partner B	10/1/2014	900	Bronze
Partner B	1/1/2015	800	Silver
Partner B	...	...	...
Partner B	11/1/2016	950	Silver
Partner B	1/1/2017	1200	Gold

I've tried using a MAXX with filtering the table

MAXX(

FILTER(

PartnerStatus,
Billings[partnerid]=PartnerStatus[partnerid] &&
YEAR(PartnerStatus[DateMovedToStatus]<=EARLIER(Billings[Year])

),
PartnerStatus[Status]

)

but that only gave me the first status the partner was in. it missed all the subsequent status changes.

Any help out there?

[Greg_Deckler · Accepted solution]

@kuraitengai Maybe:

Column = 
  VAR __Date = [CommRun]
  VAR __Partner = [PartnerName]
  VAR __StatusDate = MAXX(FILTER('PartnerStatus', [PartnerName] = __Partner && [DateMovedToStatus] <= __Date),[DateMovedToStatus])
  VAR __Status = MAXX(FILTER('PartnerStatus', [PartnerName] = __Partner && [DateMovedToStatus] = __StatusDate),[Status])
  VAR __Result = IF( __Status = BLANK(), "No Status", __Status)
RETURN
  __Result

[kuraitengai]

@Greg_Deckler 
This is great. I think that did it. Looking through the table with the calc column added to it.

I thought I did it as you typed, but i missed the < in the 

[DateMovedToStatus] <= __Date)

in the _StatusDate variable. It was only giving me silver in 2019 and gold in 2009. everything else was no status. added the <= and it filled in the years between.

Only change I made was to that variable. I did it as < instead of <=. wanted the status to not go into effect until the full calendar year after the status change. Just made that tweak, and it looks like it's working perfectly.

I'd originally tried to do it with variables, but when i read somewhere else about trying MAXX, i stopped using variables. Should have kept down that path.

Thanks for the help.

[Greg_Deckler]

@kuraitengai Variables are your friends!!