Opening Balance and Closing Balance Day wise

[venal]

Hi Team,

Greetings.!

I have query to calculate Opening Balance and Closing Balance on Daily Basis.

Ex:- 25/07/2021 -> Data available, So it will show the data based on date selection.

24/07/2021 -> No data for this date, So i need to show previous data i.e., 18/07/2021 data.

any help it would be appreciate.

Note:- I will attach the sample PBIX file for reference

[v-yangliu-msft · Accepted solution]

Hi  @venal ,

Here are the steps you can follow：

1. Take [Date] of the table DateCalendar as the slicer.

2. Create measure.

Measure =
var _select=SELECTEDVALUE('DateCalendar'[Date])
var _1=CALCULATE(SUM('BankSummary'[CLOSINGVALUE]),FILTER(ALL('BankSummary'),'BankSummary'[TRXNDATE]=_select))
var _2=
CALCULATE(MAX('BankSummary'[TRXNDATE]),FILTER(ALL('BankSummary'),'BankSummary'[TRXNDATE]<_select))
return
IF(
_1=BLANK(),CALCULATE(SUM('BankSummary'[CLOSINGVALUE]),FILTER(ALL('BankSummary'),'BankSummary'[TRXNDATE]=_2)),_1)

3. Result:

Select 25/07/2021, the data of the selected date is displayed:

Select 24/07/2021, the data displayed is 18/07/2021

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Jihwan_Kim]

Closing =

VAR _currentdate =

MAX ( DateCalendar[Date] )

VAR _lastdatenonblank =

CALCULATE (

LASTNONBLANK ( BankSummary[TRXNDATE], [closingvalue measure] ),

FILTER ( ALL ( DateCalendar ), DateCalendar[Date] <= _currentdate )

)

RETURN

CALCULATE (

[closingvalue measure],

FILTER ( ALL ( DateCalendar ), DateCalendar[Date] = _lastdatenonblank )

)

Link to the sample pbix file 

[amitchandak]

@venal , with help from date tbale you can get Last Day Non Continuous 

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

and use that if data is not available

refer if needed

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...