Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a single table and in which each row represents the debt that is owing for an account on the noted date.
Over a time period the amount of debt owing for each account can vary, when the debt amount owing changes for an account a new row appears in this table and is date stamped reflecting when this event occurs.
For arguments sake there are only four accounts and a sample dataset is as follows – practically however there are 1000’s of accounts:
ID | DebtDate | DebtOwing |
111 | 1-Oct-21 | $10 |
111 | 2-Oct-21 | $20 |
111 | 4-Oct-21 | $25 |
111 | 7-Oct-21 | $30 |
222 | 3-Oct-21 | $600 |
222 | 9-Oct-21 | $700 |
333 | 30-Sept-21 | $1000 |
333 | 29-Oct-21 | $900 |
444 | 11-Oct-21 | $100 |
444 | 15-Oct-21 | $90 |
On a given calendar date if no account’s debt changes then there are no row entries in the table for that date. If however the debt owing for an account does changes then there is a row entry for each account for which the debt has changed – if an accounts debt changes multiple times over the course of a single day only one row entry appears for that day for that account and reflects the debt owing after the last change.
I would like to be able to create a measure for the above table and which returns the debt owing that corresponds to the ‘max’ debt date for a configurable date rate e.g. if the date range selected is [1-Oct-21] to [10-Oct-21] then I can use this measure to create a table in my report UI as follows:
ID | Most Recent Debt Date | Most Recent Debt Owing |
111 | 7-Oct-21 | $30 |
222 | 9-Oct-21 | $700 |
I would also like to be able to create a PowerBI Card in my report UI that would use the same measure (?) or instead via a separate measure it would return the value $730 – this being the sum of $90 plus $300 in the above example.
Unfortunately I am struggling to get my head around DAX/Power query and have gone a little nuts trying to solve the above, accordingly any help and guidance that can be offered will be really (really) appreciated, thanks!
Solved! Go to Solution.
Here is one way to do it. Put your ID and DebtDate columns in a table visual and don't summarize either, along with this measure. Result is shown below (note your sample data didn't have the 9/9 row).
Latest Debt =
VAR thisdebtdate =
MAX ( Debt[DebtDate] )
VAR maxseldate =
CALCULATE ( MAX ( Debt[DebtDate] ), ALLSELECTED ( Debt[DebtDate] ) )
VAR result =
CALCULATE ( MAX ( Debt[DebtOwing] ), Debt[DebtDate] = maxseldate )
RETURN
IF ( thisdebtdate = maxseldate, result )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Attached is a Power Query solution which should get you most of the way there.
Let me know if you have questions.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
KNP, thank you for taking the time to reply 🙂
In doing so however it doesn’t quite solve the conundrum I am trying to solve, sorry.
To explain, I have expanded the data table to now and in addition include for-example data for September:
ID | DebtDate | DebtOwing |
111 | 1-Oct-21 | $10 |
111 | 2-Oct-21 | $20 |
111 | 4-Oct-21 | $25 |
111 | 7-Oct-21 | $30 |
222 | 3-Oct-21 | $600 |
222 | 9-Oct-21 | $700 |
333 | 30-Sep-21 | $1000 |
333 | 29-Oct-21 | $900 |
444 | 11-Oct-21 | $100 |
444 | 15-Oct-21 | $90 |
111 | 1-Set-21 | $110 |
111 | 3-Set-21 | $111 |
222 | 2-Set-21 | $660 |
222 | 7-Sep-21 | $560 |
333 | 4-Set-21 | $9900 |
444 | 17-Sep-21 | $330 |
If given the above the start/end date is changed to [1-Sept-21] to [10-Sept-21] then the data rows of interest are the highlighted-in-green rows below – for the original date range of [1-Oct-21] to [10-Oct-21] then the highlighted-in-yellow rows are of interest:
What I don’t know is advance is what the date range will be that the end user sets to filter the data hence then I cant statically persist the max value for an ID in a table, it needs to be calculated dynamically.
Were I to solve this in SQL I would write a Query #1 below to return a table dataset and Query #2 to give the overall value, accordingly I think that I need a measure that can power-query this on the fly alas though I cant for the life of me figure out how to do this, tears!
Here is one way to do it. Put your ID and DebtDate columns in a table visual and don't summarize either, along with this measure. Result is shown below (note your sample data didn't have the 9/9 row).
Latest Debt =
VAR thisdebtdate =
MAX ( Debt[DebtDate] )
VAR maxseldate =
CALCULATE ( MAX ( Debt[DebtDate] ), ALLSELECTED ( Debt[DebtDate] ) )
VAR result =
CALCULATE ( MAX ( Debt[DebtOwing] ), Debt[DebtDate] = maxseldate )
RETURN
IF ( thisdebtdate = maxseldate, result )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Pat, wow-ee, so simple, so elegant! Big BIG thank you for your response and the solution. Today is now a Happy Day and me here will be spending time now understanding more the mighty CALCULATE function!!
KNP, thank you for taking the time to reply 🙂
In doing so however it doesn’t quite solve the conundrum I am trying to solve, sorry.
To explain, I have expanded the data table to now and in addition include for-example data for September:
ID | DebtDate | DebtOwing |
111 | 1-Oct-21 | $10 |
111 | 2-Oct-21 | $20 |
111 | 4-Oct-21 | $25 |
111 | 7-Oct-21 | $30 |
222 | 3-Oct-21 | $600 |
222 | 9-Oct-21 | $700 |
333 | 30-Sep-21 | $1000 |
333 | 29-Oct-21 | $900 |
444 | 11-Oct-21 | $100 |
444 | 15-Oct-21 | $90 |
111 | 1-Set-21 | $110 |
111 | 3-Set-21 | $111 |
222 | 2-Set-21 | $660 |
222 | 7-Sep-21 | $560 |
333 | 4-Set-21 | $9900 |
444 | 17-Sep-21 | $330 |
If given the above the start/end date is changed to [1-Sept-21] to [10-Sept-21] then the data rows of interest are the highlighted-in-green rows below – for the original date range of [1-Oct-21] to [10-Oct-21] then the highlighted-in-yellow rows are of interest:
What I don’t know is advance is what the date range will be that the end user sets to filter the data hence then I cant statically persist the max value for an ID in a table, it needs to be calculated dynamically.
Were I to solve this in SQL I would write a Query #1 below to return a table dataset and Query #2 to give the overall value, accordingly I think that I need a measure that can power-query this on the fly alas though I cant for the life of me figure out how to do this, tears!
I've started working on a DAX solution but I don't have the time I'd need to finish it.
In case it is useful to you, this is where I got to but it's not working correctly based on the max date.
MaxDebt =
VAR MinDate =
CALCULATE (
MIN ( 'Table'[DebtDate] ),
ALLSELECTED ( 'Date'[Column1] )
)
VAR MaxDate =
CALCULATE (
MAX ( 'Table'[DebtDate] ),
ALLSELECTED ( 'Date'[Column1] )
)
VAR MaxDebt =
CALCULATE (
MAX ( 'Table'[DebtOwing] ),
FILTER (
'Date',
'Date'[Column1] >= MinDate
&& 'Date'[Column1] <= MaxDate
)
)
VAR TotalMaxDebt =
SUMX (
SUMMARIZE (
'Table',
'Table'[ID],
"MV", MAX ( 'Table'[DebtOwing] )
),
[MV]
)
RETURN
IF (
HASONEVALUE ( 'Table'[ID] ),
MaxDebt,
TotalMaxDebt
)
There's a chance I'm over complicating this.
Someone with stronger DAX Fu skills will probably get you an answer pretty quickly.
(these guys spring to mind)
@AlexisOlson, @PaulDBrown, @mahoneypat, @TomMartens - can someone show @wowthisistricky (and me) how this should be done.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
No, you're right, that was never going to work, sorry.
I'll see if I can figure out the DAX.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.