cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Return Status at the time of a billing month

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?

1 ACCEPTED SOLUTION
Super User

@Anonymous 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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

@Anonymous 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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors