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 coun...
  • 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

tamerj1's avatar
tamerj1
Community 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?