Help inferring an item's status when there is no existing data point

Amybot — Wed, 22 Feb 2023 18:05:33 GMT

Hi everyone! 😀 I'm a long time user of the forums, but a first time poster.
I have a situation where I have data coming from another system (Azure) - a source I can't change or adapt and I need to be able to count 'in progress' items to say what was being worked on at any given moment in time.
However things can sit idle for extended periods, so it's in progress but not 'active' so I can have the situation where there's no update/record to count -

For example item 1 is open in September, and is still open in November, so I need to infer that it was also open in October.

ChangedDate	ID	Status	Month
30/09/2022	1	In progress	Sep 2022
30/11/2022	1	In progress	Nov 2022
30/9/2022	2	In progress	Sep 2022
30/10/2022	2	In progress	Oct 2022
31/11/2022	2	In progress	Nov 2022

However all I'm able to get is :

Month	CountItems
Sep 2022	2
Oct 2022	1
Nov 2022	2

But what I want to see is:

Month	CountItems
Sep 2022	2
Oct 2022	2
Nov 2022	2

Is there a way to get the last status to persist into the next month in the case that there's no actual update?

Thanks
Amy.

Re: Help inferring an item's status when there is no existing data point

Anonymous — Fri, 24 Feb 2023 10:09:37 GMT

Hi Amybot ,

you can create a measure as below to get it, please find the details in the attachment.

CountItems = VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ChangedDate] <= _seldate ) )

In addition, you can refer the below blog to add the "missing" dates for per ID in Power Query Editor:

Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Patrick | Medium

After you completed the above step, it will be easier to get the expected count...

Best Regards

topic Re: Help inferring an item's status when there is no existing data point in DAX Commands and Tips

Help inferring an item's status when there is no existing data point

Re: Help inferring an item's status when there is no existing data point