Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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?
Solved! Go to Solution.
@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
@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
@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.
@Anonymous Variables are your friends!!
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |