Forum Discussion

dstanisljevic's avatar
dstanisljevic
Helper I
6 years ago
Solved

Running Count per Day

Hello,   I'm trying to a take the following table and create a very specific measure:   ID Status LastUpdate 1 Solved 03/15/2019 2 Open 05/14/2020 3 Open 01/12/2020 4 Solv...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi dstanisljevic ,

     

    We can use the following steps to meet your requirement.

     

    1. Create a date table and there is no relationship between date table and table.

     

    Date = CALENDAR("2019/1/1","2020/12/31")

     

     

    2. Create a measure to calculate the count.

     

    Measure = 
VAR date_ =
    MIN ( 'Date'[Date] )
VAR x =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        x < MAX ( 'Table'[LastUpdate] ),
        DISTINCTCOUNT ( 'Table'[ID] )
            - CALCULATE (
                DISTINCTCOUNT ( 'Table'[ID] ),
                FILTER ( 'Table', 'Table'[Status] = "Solved" && 'Table'[LastUpdate] < date_ )
            ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[ID] ),
            FILTER ( 'Table', 'Table'[Status] = "Solved" && 'Table'[LastUpdate] = date_ )
        )
)

     

    3. Then create a table visual, put the date[date] and [Measure] to values, and add a slicer based on date[date].

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Please try this measure.  I assumed you also need to include the CreatedDate to make sure you don't count future created ones.

 

NewMeasure = var selecteddate = SELECTEDVALUE('Date'[Date])
return CALCULATE(DISTINCTCOUNT(Solved[ID]), ALL(Solved), Solved[LastUpdate]>selecteddate, Solved[CreatedDate]< selecteddate)
 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

dstanisljevic's avatar
dstanisljevic
Helper I
6 years ago

I'm getting this error: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

 

I did modify as the filter would need some restrictions. It should only be counted if the status is not solved or if the status is solved and the last update is less than the selected date:

 

Measure  =
     var SelectedDate = SELECTEDVALUE('Calendar'[Date])
     return CALCULATE(
          DISTINCTCOUNT('Table'[ID]),
          ALL('Table'),
          ('Table'[Status]<>"Solved" || ('Table'[Status]="Solved" && 'Table'[Last Update]<SelectedDate))
)
  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    dstanisljevic 

     

    Try this 

    Measure =
VAR SelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "Solved"
                || ( 'Table'[Status] = "Solved"
                && 'Table'[Last Update] < SelectedDate )
        )
    )



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂