Count rows before current day in visual

[baekelal]

Hi,

I have a table with information about incidents. For example created and solved date. That table is connected to a date table via the created date.

I have now a visual where i easly can count the created incident/per day. 

The problem comes with the Solved incidents. If i count the rows on the Solve date it shows solved incidents on that day but also solved incident that are solved on a later date. See Table. How can make powerbi only counts the rows where the solved (opgelost) date is equal to the date in the chart.

See example off the bar chart.

2nd follow up question but also related.

How can i count rows from dates before the day in the bar chart.

Thank in advance

Greetings.

[tamerj1]

Hi @baekelal 
for running total you can use the following

MesureRT =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'Table'[Resoved Date], 'Date'[Date] ),
        'Date'[Date] <= CurrentDate
    )

and based on Incident Date

Mesure1 =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Date'[Date] <= CurrentDate
    )

[baekelal]

Hi again @tamerj1  and thanks alot !

The running total for created incidents works.

Only the running total for the solved incident is not correct. It starts way too high.

This is de Dax formula

Totaal aantal opgeloste incidenten =

VAR CurrentDate =

MAX ( Kalender[Datum] )

RETURN

CALCULATE (

COUNTROWS ( 'Incidenten' ),

USERELATIONSHIP ( Incidenten[Opgelost],Kalender[Datum] ),

Kalender[Datum] <= CurrentDate

)

It starts immediatly on 784 where it should start around 3.

What is still wrong ?

[tamerj1]

Hi @baekelal ,
Try this

Totaal aantal opgeloste incidenten =
VAR CurrentDate =
    CALCULATE (
        MAX ( Kalender[Datum] ),
        USERELATIONSHIP ( Incidenten[Opgelost], Kalender[Datum] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Incidenten' ),
        USERELATIONSHIP ( Incidenten[Opgelost], Kalender[Datum] ),
        Kalender[Datum] <= CurrentDate
    )

[baekelal]

Hi @tamerj1 ,

Same result. It didn't change anything.

[tamerj1]

@baekelal 
Do yu have blank dates? Try this

Totaal aantal opgeloste incidenten =
VAR CurrentDate =
    MAX ( Kalender[Datum] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Incidenten' ),
        USERELATIONSHIP ( Incidenten[Opgelost], Kalender[Datum] ),
        Kalender[Datum] <= CurrentDate,
        NOT ISBLANK ( Incidenten[Opgelost] )
    )

[tamerj1]

@baekelal 
Did you try this?

[tamerj1]

@baekelal 
Can you please share sample file?

[baekelal]

Hi,

Hope 50 lines tell's enough

Nummer	Geopend	Opgelost
INC0010002	24/01/2022 0:00	24/01/2022 0:00
INC0010003	24/01/2022 0:00	24/01/2022 0:00
INC0010006	24/01/2022 0:00	24/01/2022 0:00
INC0010008	24/01/2022 0:00
INC0010019	25/01/2022 0:00	26/01/2022 0:00
INC0010012	25/01/2022 0:00	25/01/2022 0:00
INC0010021	26/01/2022 0:00	26/01/2022 0:00
INC0010024	26/01/2022 0:00	26/01/2022 0:00
INC0010026	26/01/2022 0:00
INC0010033	27/01/2022 0:00
INC0010041	28/01/2022 0:00	3/02/2022 0:00
INC0010039	28/01/2022 0:00	28/01/2022 0:00
INC0010096	31/01/2022 0:00	18/02/2022 0:00
INC0010095	31/01/2022 0:00	15/02/2022 0:00
INC0010100	31/01/2022 0:00	15/02/2022 0:00
INC0010056	31/01/2022 0:00	14/02/2022 0:00
INC0010084	31/01/2022 0:00	14/02/2022 0:00
INC0010099	31/01/2022 0:00	11/02/2022 0:00
INC0010067	31/01/2022 0:00	9/02/2022 0:00
INC0010073	31/01/2022 0:00	9/02/2022 0:00
INC0010057	31/01/2022 0:00	8/02/2022 0:00
INC0010072	31/01/2022 0:00	8/02/2022 0:00
INC0010079	31/01/2022 0:00	8/02/2022 0:00
INC0010088	31/01/2022 0:00	8/02/2022 0:00
INC0010058	31/01/2022 0:00	7/02/2022 0:00
INC0010064	31/01/2022 0:00	7/02/2022 0:00
INC0010055	31/01/2022 0:00	4/02/2022 0:00
INC0010065	31/01/2022 0:00	4/02/2022 0:00
INC0010069	31/01/2022 0:00	4/02/2022 0:00
INC0010077	31/01/2022 0:00	4/02/2022 0:00
INC0010089	31/01/2022 0:00	4/02/2022 0:00
INC0010092	31/01/2022 0:00	4/02/2022 0:00
INC0010060	31/01/2022 0:00	2/02/2022 0:00
INC0010066	31/01/2022 0:00	2/02/2022 0:00
INC0010083	31/01/2022 0:00	2/02/2022 0:00
INC0010104	31/01/2022 0:00	2/02/2022 0:00
INC0010050	31/01/2022 0:00	1/02/2022 0:00
INC0010103	31/01/2022 0:00	31/01/2022 0:00
INC0010048	31/01/2022 0:00
INC0010049	31/01/2022 0:00
INC0010051	31/01/2022 0:00
INC0010052	31/01/2022 0:00
INC0010053	31/01/2022 0:00
INC0010054	31/01/2022 0:00
INC0010059	31/01/2022 0:00
INC0010061	31/01/2022 0:00
INC0010063	31/01/2022 0:00
INC0010068	31/01/2022 0:00
INC0010070	31/01/2022 0:00

[tamerj1 · Accepted solution]

@baekelal 
Now is clear. You have alot of zeros. All records with zero "Oplgelost" Date will line below any selected date. This shall solve the problem

Totaal aantal opgeloste incidenten =
VAR CurrentDate =
    MAX ( Kalender[Datum] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Incidenten' ),
        USERELATIONSHIP ( Incidenten[Opgelost], Kalender[Datum] ),
        Kalender[Datum] <= CurrentDate,
        NOT ISBLANK ( Incidenten[Opgelost] ),
        Incidenten[Opgelost] <> 0
    )

I kept the NOT ISBLANK condition incase you have blank dates. Please check and let me know

[baekelal]

This works !!!

Thanks alot.

[tamerj1]

Hi @baekelal 

You can create another "inactive" relationship between 'Date'[Date] and 'Table'[Resoved Date] then use 

CALCULATE ( COUNTROWS ( 'Table' ), USERELATIONSHIP ( 'Table'[Resoved Date], 'Date'[Date] ) )

the 2nd question is not clear. Are you trying to calculate the running totals?

[baekelal]

Hi @tamerj1 ,

Thanks alot for this first solution. It worked like a charm!.

For my 2nd questions:

Yes i want to calculate running totals. My goals is to have the open incidents for each day.

Therefore i would like to calculate the accumulated total created incidents decreased with the accumulated total solved incidents and that for each day.

Does this explanation make more sence?