Created daily balancing table - how to calculate BOD and EOD values

Anonymous — Fri, 25 Feb 2022 20:51:48 GMT

I have a Data Table showing history of status changes for transactions. Each transaction has entries only on days that the status changed. Each record also includes the date that the transaction is closed.
A simple example:

Status Change Date	Transaction Status	Next Close Date
2022-01-30	Created	2022-02-01
2022-02-01	Completed	null
2022-02-02	Reopen	null

I would like to create a visualization shows that derives the following measures for each calendar day.

Date	Open BOD	New	Reopen	Closed	Open EOD
2022-01-30	0	1	0	0	1
2022-01-31	1	0	0	0	1
2022-02-01	1	0	0	1	0
2022-02-02	0	0	1	0	1

I can easily obtain the New, Reopen and Close values, but am struggling with the Open BOD and Open EOD values, since I need to derive values even if there isn't a change in status for the date, e.g. 2022-01-31 has BOD & EOD .

Open at Beginning of Day
Next Close Date is blank or Next Close Date >= date shown in the resulting table

Open at End of Day
Next Close Date is blank or Next Close Date > date shown in the resulting table

I was originally thinking of generating the date in the resulting table, but could just use the distinct list of Status Change Dates.

Can anyone help me figure out how to calculate these two sums?

Re: Created daily balancing table - how to calculate BOD and EOD values

lbendlin — Sun, 27 Feb 2022 18:46:54 GMT

you will need a disconnected dates table and then do a crossjoin.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: Created daily balancing table - how to calculate BOD and EOD values in DAX Commands and Tips

Created daily balancing table - how to calculate BOD and EOD values

Re: Created daily balancing table - how to calculate BOD and EOD values