Hi,
Apologies if this has been answered but all the combinations of terms I searched for came up negative.
I'm trying to graph balance sheet items by period, for this year and for last year, but filter only the current year.
I've uploaded a simplified version of the file at:
https://drive.google.com/open?id=1M9EQwcP9udlTjB9Hd1CsvbUFwrdZiEC7
In this file there are 3 tables:
- Ledger: this is the equivalent of the General Ledger transactions in the accounting system. The balance sheet figures are cumulative, as each line in the ledger represents a movement of the General Ledger, so P3 will be Opening/Historic balances + P1 value + P2 + P3.
- GL Map: this maps the GL codes to named financial items e.g. Stock, Cash, Revenue, Staff Costs etc.
- Period_Filter: this is just a list of periods, 1 to 12. This is needed in the main file as there are a number of other databases besides ledger (e.g. budget) that this is used to filter across all datasets.
I have two measures, which calculate the cumulative figures for this year and last year:
BS LY = CALCULATE(
SUM( 'Ledger v1'[Value ]) ,
FILTER(
ALLEXCEPT('Ledger v1', 'GL map v1'[Line Item]),
'Ledger v1'[period] <= MAX(Period_Filter[Period]) && 'Ledger v1'[FY] = "2019" || 'Ledger v1'[FY] = "Historic"
) )which calculates the cumulative GL values up to the prior year's period filter, and:
BS TY = CALCULATE(
SUM( 'Ledger v1'[Value ]) ,
FILTER(
ALLEXCEPT('Ledger v1', 'GL map v1'[Line Item]),
'Ledger v1'[period] <= MAX(Period_Filter[Period]) && 'Ledger v1'[FY] = "2020" || 'Ledger v1'[FY] = "2019" || 'Ledger v1'[FY] = "Historic"
))Which calculates the cumulative values up to this year's period filter.
The ALLEXCEPT in the main file has many more filters kept, but I've just got the one here for this example.
In the current setup, if I want to look at P3 results, I filter to P3 and the graphs of Stock/Debtors/Cash map the first 3 periods, TY vs LY.
See: https://drive.google.com/open?id=1SpalthYblC9YJb87Agt4MzA_tCt6-CWs
However, i would like the graph to show the remaining periods of LY too. (so filtering period 1 to 3 shows 1-3 for TY and 1-12 for LY)
You can extend the filter to period 12, and it shows the LY to P12, but then it also shows TY to P12 as well - and since the balances are cumulative, it effectively shows whatever the last entries in the ledger are for the remaining periods.
I come from an Excel background and this one is stumping me!
I tried creating a second "period" filter, that you filter on - so the graph still divides the bars by period, of which all are shown, but then based on this second filter, if this is set to 1-3, then 4+ would be zero - but couldn't figure out how!
Effectively I'm trying to come up with a way to filter the TY figures separately to the LY figures. When i tried the approach above with a second period filter, the TY bar from P4 onwards just displayed whatever the P3 figures were at that time.
Thanks in advance - let me know if any of you guys have managed to achieve something similar, as I feel like this is fairly standard for Finance Packs.
Harry
