Forum Discussion

baekelal's avatar
baekelal
Frequent Visitor
4 years ago
Solved

Count rows before current day in visual

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's avatar
    tamerj1
    4 years ago

    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

12 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      baekelal
      Frequent Visitor
      4 years ago

      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?

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      baekelal
      Frequent Visitor
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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
    )