topic Re: Count rows before current day in visual in DAX Commands and Tips

Count rows before current day in visual

baekelal — Sun, 20 Feb 2022 17:59:24 GMT

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.

Re: Count rows before current day in visual

tamerj1 — Sun, 20 Feb 2022 18:15:49 GMT

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?

Re: Count rows before current day in visual

baekelal — Mon, 21 Feb 2022 03:11:00 GMT

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?

Re: Count rows before current day in visual

tamerj1 — Mon, 21 Feb 2022 03:51:42 GMT

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 )

Re: Count rows before current day in visual

baekelal — Mon, 21 Feb 2022 04:24:31 GMT

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 ?

Re: Count rows before current day in visual

tamerj1 — Mon, 21 Feb 2022 05:30:15 GMT

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 )

Re: Count rows before current day in visual

baekelal — Mon, 21 Feb 2022 06:36:54 GMT

Hi tamerj1 ,

Same result. It didn't change anything.

Re: Count rows before current day in visual

tamerj1 — Mon, 21 Feb 2022 06:46:46 GMT

baekelal
Can you please share sample file?

Re: Count rows before current day in visual

tamerj1 — Mon, 21 Feb 2022 06:53:39 GMT

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

Re: Count rows before current day in visual

baekelal — Mon, 21 Feb 2022 06:59:59 GMT

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

Re: Count rows before current day in visual

tamerj1 — Mon, 21 Feb 2022 07:32:22 GMT

baekelal
Did you try this?

Re: Count rows before current day in visual

tamerj1 — Mon, 21 Feb 2022 07:38:16 GMT

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

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

Re: Count rows before current day in visual

baekelal — Mon, 21 Feb 2022 08:17:55 GMT

This works !!!

Thanks alot.