Cumulative FYTD Opening Balance

DAX DAX Calculations
1

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" ) ) ) )

CAB FYTD 20200630 Cash Rec Bal 20200831

2

Hi @mkaess

Could you please help to share your sample pbix file, so that forum member can give you quick reply.

3

I’ll clean it up and do some redaction first then upload a sample.

4

PBIX file uploaded… :slight_smile:Open_Close Balance Example.pbix (765.7 KB)

5

Worked it out using this measure:

Cash Opening Balance FY =
VAR EOFY = DATEADD( ENDOFYEAR( Dates[Date], “06-30” ), -1, YEAR )

RETURN
IF(
SELECTEDVALUE( Dates[Date] ) > EOFY,
BLANK(),
CALCULATE(
[Cash at Bank Total],
FILTER( ALLSELECTED( ‘Dates’ ),‘Dates’[Date] <= EOFY )
)
)