Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Amybot
New Member

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

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. 

ChangedDateIDStatusMonth
30/09/20221In progressSep 2022
30/11/20221In progressNov 2022
30/9/20222In progressSep 2022
30/10/20222In progressOct 2022
31/11/20222In progressNov 2022

 

However all I'm able to get is :

MonthCountItems
Sep 20222
Oct 20221
Nov 20222

 

But what I want to see is:

MonthCountItems
Sep 20222
Oct 20222
Nov 20222


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.

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

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

 

yingyinr_2-1677233260276.png

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-Pat...

yingyinr_0-1677232205132.png

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

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors