Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |