Forum Discussion
Robert1981
Helper III
Helper IIISubtotal / total under switch categories
Hi All, I am using the SWITCH function. I created a separate table and I am able to display the categories on the Y-Axis while I show time on the X-axis. However the row totals don't seem to wor...
Robert1981Helper III
Helper IIIThank you.
It didn't quite work for me though. It is hard to create a sample data as the report is linked.
Here is what I did:
The table is a huge table with all postings to the Accounts Payable Subledger (both new invoices and received payments). Based on the date an item was cleared, and the end of month calculation, it calculates the open balance.
The for the table in the first post, I break down the open balance by how many days items are open.
I use the following formulas:
Basic Open Balance:
Open AP =
VAR CurrentDate = MAX('DATE'[Date])
VAR Open_Invoice =
Calculate(
Sum('Subledger AP'[Amount_USD]),
ALL('DATE'),
'Subledger AP'[Posting Date.1]<=CurrentDate,
ISBLANK('Subledger AP'[Actuals Clearing Date(Date Format)])
||'Subledger AP'[Actuals Clearing Date(Date Format)]>CurrentDate
)
RETURN
Open_Invoice*-1
The I created a separate table with the breakdown of the days, to be used in the switch:
The I created a separate table with the breakdown of the days, to be used in the switch:
Aging_Analysis =
var selectedComp = SELECTEDVALUE(FA_Breakdown[Value1])
var CurrentDate=Max('DATE'[Date])
return
SWITCH(selectedComp,
"I. 0-30 Days",
Calculate([Open AP],
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=0 &&
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)>-31),
"II. 31-60 Days",
Calculate([Open AP],
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=-31 &&
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)>-61),
"III. 61-90 Days",
Calculate([Open AP],
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=-61 &&
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)>-91),
"IV. >90 Days",
Calculate([Open AP],
Datediff('Subledger AP'[Net Due Date(Date Format)],Currentdate,day)<=-91)
)