I have a balance sheet type report where I need to show the opening/closing balances for cash receipts by MTD and FYTD. My MTD calculations work well but I’m stuck on the Opening Balance FYTD measure. Attached are two screenshots, one of the visual and another of the cumulative balances and daily totals. No matter what I’ve tried, I cannot get the Opening FYTD value to work.
There is a ‘before’ date slicer on the report page that is set to 31/08/2020. Our FY starts July 1.
I’m using calculation groups for the MTD/FYTD columns otherwise I’d have to create about 20 individual measures. OPENINGBALANCEYEAR doesn’t work. It seems to be simply totaling the Cash at Bank Total Column and not acting returning the cumulative total.
The FYTD calculation items is:
IF( ISSELECTEDMEASURE( [Cash Closing Balance] ), SELECTEDMEASURE(), IF( ISSELECTEDMEASURE( [Cash Opening Balance] ), SELECTEDMEASURE(), CALCULATE( TOTALYTD( SELECTEDMEASURE(), Dates[Date], "06-30" ) ) ) )