cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Running Count per Day

Hello,

 

I'm trying to a take the following table and create a very specific measure:

 

IDStatusLastUpdate
1Solved03/15/2019
2Open05/14/2020
3Open01/12/2020
4Solved05/16/2020
5Solved05/16/2020

 

I have a seperate table I built as my date table. What I'm trying to calculate is determining for any given day on the calendar, what count of IDs where they are not Solved and considered to be opened. The current status may state Solved, however, the last updated date for the column tells me when it was solved. This would mean that if the update date was equal to the calendar date or greater and the status is Solved, any date prior to the last update it would be counted as opened and after that as solved.

 

So if I built a table visual, I'd expect to see something like this:

 

DateCount
05/14/20204
05/15/20204
05/16/20202

 

I do have a seperate column in the table that is the date the record was created. This is set as the relationship for the date table. It seems that my current attempts at creating this measure are being limited to that relationship, as so far the count numbers match up exactly with the reported dates only and not the overall of all the records in the table.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Running Count per Day

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

 

Run 1.jpg

 

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

 

Run 2.jpg

 

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.

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Running Count per Day

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

Highlighted
Helper I
Helper I

Re: Running Count per Day

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))
)
Highlighted
Community Champion
Community Champion

Re: Running Count per Day

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

Highlighted
Microsoft
Microsoft

Re: Running Count per Day

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

 

Run 1.jpg

 

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

 

Run 2.jpg

 

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.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors