Continuous Count of ID based on the Submission and Approval Date

[William_Walk]

Hi 

I'm struggling with a problem regarding Contious counting of ID's. 

The ask is to continually count per month the ID's in the table based on if its been submitted but not approved.

e.g if it was submitted in january 2023 and approved in march 2023 we would count per month for jan, feb and march until its been approved. 

The aim is to show a trend in unapproved ID's over time.

The columns im using are the following:

• ID
• Submission Date
• Approval Date 

I have tried the below dax code commands to solve this:

Any help is greatly appreciated 🙂 

[Jihwan_Kim]

Hi, 

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: =
COUNTROWS (
    FILTER (
        milestone,
        milestone[submission_date] <= MAX ( 'calendar'[Date] )
            && milestone[approval_date] >= MIN ( 'calendar'[Date] )
    )
)

[William_Walk]

Hi

This is brilliant, is it possible to exclude ID's that have been approved in the same month as well? e.g if they were submitted in january and approved in january then we wouldnt count the ID.

Thanks for the help on this 🙂

[Jihwan_Kim]

Hi,

Please provide your sample pbix file's link, and then I can try to look into your sample pbix file.

Thank you.