How to return status of previous record if there is no data found in the period chosen

[mhng]

Hi,

In my model, I have two table, one fact and one date not linking. The date table is meant to provide a continuity report such that even though there is not record, it should show distinct count of ID as 1 if the status remains O or R.

My challenge is to figure out how to include this count when there is no transaction.

For example, if user picked 2015 October to November, it should still show 1 because the status for previous record "2015-09-22" is "O"  (O=Open,F=Final, R=Reopen)

id	date	status
151234	2015-09-22	O
151234	2015-09-22	O
151234	2015-12-04	O
151234	2015-12-18	O
151234	2016-09-02	O
151234	2016-11-29	O
151234	2016-12-21	O
151234	2018-10-16	O
151234	2018-10-16	O
151234	2018-10-22	O
151234	2018-10-31	O
151234	2018-11-30	O
151234	2019-01-21	O
151234	2019-01-21	O
151234	2019-01-21	O
151234	2019-01-21	O
151234	2019-01-21	F
151234	2019-01-31	R
151234	2019-03-14	F

Thanks for your attention and any help would be appreciated!!!

Cheers, Michelle

[amitchandak]

Please refer

https://community.powerbi.com/t5/Desktop/get-value-from-Previous-row/td-p/441336

https://community.powerbi.com/t5/Desktop/Reference-previous-row-to-get-value/td-p/154824

https://community.powerbi.com/t5/Desktop/Get-previous-row-value-in-new-column/td-p/496182

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.