Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I'm hoping someone may be able to assist.
I have a list of invoice #'s with invoice date and amount, and also a calendar table with a relationship between the invoice date and calendar date.
On the report, I want to be able to select the calendar date using a slicer, and have a table populate the age of the invoice (per selected date), and summarise the invoice amounts by an "age bracket" within a matrix.
I have achieved two things thus far:
1) I have been able to calculate the invoice age per selected slicer date using the following measure:
2) I have created a measure with a switch formula to distribute ages between age brackets:
Create a disconnected table like the one below (no relationships). Sort Age Bracket by Age Bracket Index.
Create measures:
Sum of Total = SUM ( ARITEMA2[Total] )
AgeBrackets =
VAR vLowerBound =
SELECTEDVALUE ( AgeBracket[Lower Bound] )
VAR vUpperBound =
SELECTEDVALUE ( AgeBracket[Upper Bound] )
VAR vAge = [Age2]
VAR vResult =
IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] )
RETURN
vResult
Add AgeBracket[Age Bracket] to matrix columns, and [AgeBrackets] to matrix values. You can expand this example to handle totals with a measure like this:
AgeBrackets =
VAR vLowerBound =
SELECTEDVALUE ( AgeBracket[Lower Bound] )
VAR vUpperBound =
SELECTEDVALUE ( AgeBracket[Upper Bound] )
VAR vAge = [Age2]
VAR vResult =
SUMX ( VALUES ( HEADER[OPS] ), IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] ) )
RETURN
vResult
Proud to be a Super User!
Hi @DataInsights , thank you for your assistance. This has certainly got me a lot further - I have one issue though. On my actual data set, theres about 4 years worth of invoicing. This solution seems to be lumping everything into the max bracket within the category (everything is being categorised as 120+).
Wondering if there is a quick solution for that?
Thanks in advance.
Would you be able to provide an example of data that appears in the wrong category? You could add additional rows to the shared pbix if that's easier.
Proud to be a Super User!
Hi @DataInsights ,
PBIX link here: TEST - Debtors Point in Time2.pbix
Hopefully you can assist.
Thanks,
Mitch
Try this measure:
AgeBrackets =
VAR vLowerBound =
SELECTEDVALUE ( AgeBracket[Lower Bound] )
VAR vUpperBound =
SELECTEDVALUE ( AgeBracket[Upper Bound] )
VAR vMaxDate =
MAX ( DateTable[Date] )
VAR vResult =
SUMX (
Invoicing,
VAR vInvDate = Invoicing[Date]
VAR vAge = DATEDIFF ( vInvDate, vMaxDate, DAY )
RETURN
IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] )
)
RETURN
vResult
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
13 | |
12 | |
12 | |
9 |