Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Frank Wheeler]

I currently have a measure that calculates DistinctCount of an ID when has a Start Status (date/time) and End Status (date/time) is in the range or if the “Status.Next” = STOP at the end of the month.  I am having trouble converting this to count the IDs in the same way but based upon the start of the week (Sunday).  I do have a “Start of Week” defined in my date table as a a Date/Time data type. 

My monthly measure that currently works is as follows:

Backlog =

                CALCULATE (

                                DISTINCTCOUNT(Backlog[ID]),

                                                FILTER(Backlog,

                                                                Backlog[Start_Status]<=ENDOFMONTH(‘Date'[EOMTime])
                                                                &&(Backlog[End_Status]>=ENDOFMONTH(‘Date'[EOMTime])

                                                                ||(Backlog[Status.Next]=”STOP”)

                                                                )

                                                                &&(Backlog[Status]=”In Progress”)
                                                )

                                )

I am trying to avoid creating a calculated table.  Performance seems to be affected when I do that since this data is derived from an audit table.  I have created a solution with a calculated table but was wanting to strictly use measures only.  Any help would be greatly appreciated.

——————————
Frank Wheeler
——————————