Forum Discussion
How to pull a balance date and master data date
Hi bweiland ,
I understand. Below is the final image of the output. Is this what you are looking for when you visual? This was done bascially by inputing an index column in the original table just like the one shown in the second image below. This was created to uniquely identify every row.
I'll leave the link to the file to check out the measures and columns. Thanks
https://drive.google.com/file/d/1EL4CyHjGUa3D__w7byn3FhXLteC-WBvX/view?usp=sharing
Hi bweiland,
Thank you for reaching out to the Microsoft fabric community forum.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
Dax Measure for Latest Master Data date:
Latest MasterData Date =
VAR SelectedDate = MAX(Balance[BalanceDate])
VAR CurrentAcct = SELECTEDVALUE(Balance[AccountNumber])
RETURN
CALCULATE (
MAX(MasterData[MasterDate]),
FILTER (
MasterData,
MasterData[AccountNumber] = CurrentAcct &&
MasterData[MasterDate] <= SelectedDate
)
)
Dax Measure for Latest Risk Rating:
Latest Risk Rating =
VAR SelectedDate = MAX(Balance[BalanceDate])
VAR CurrentAcct = SELECTEDVALUE(Balance[AccountNumber])
VAR LatestDate =
CALCULATE (
MAX(MasterData[MasterDate]),
FILTER (
MasterData,
MasterData[AccountNumber] = CurrentAcct &&
MasterData[MasterDate] <= SelectedDate
)
)
RETURN
CALCULATE (
MAX(MasterData[RiskRating]),
FILTER (
MasterData,
MasterData[AccountNumber] = CurrentAcct &&
MasterData[MasterDate] = LatestDate
)
)
Relationship between both tables:
outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Hi bweiland,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
This did not work.
1) There is reference to a 'MasterData[RiskRating] field which I do not have.
2) When I tried the Latest MasterData Date measure it produced a result for 2025-02-28 of $400 for Dog which is the correct Master data dimension 'Dog' but incorrect balance. It seems the measure is cummulative sum versus being just $200.
I found that creating the below calculated column results in the desired result:
Animal Type Column =VAR CurrentAccount = 'Fact'[Account Number]VAR CurrentFactDate = 'Fact'[Date]VAR ClosestDateRow =TOPN(1,FILTER(MasterData,MasterData[Account Number] = CurrentAccount),ABS(DATEDIFF(Masterdata[Date], CurrentFactDate, DAY)),ASC)RETURNMAXX(ClosestDateRow, MasterData[Animal Type])
