Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

Measure to Distinct Count IDs With Start and End Date/Times That occur on the Start of the Week

  • 1.  Measure to Distinct Count IDs With Start and End Date/Times That occur on the Start of the Week

    Posted Jun 28, 2022 04:07 PM

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