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

baekelal 
Can you please share sample file?

baekelal's avatar
baekelal
Frequent Visitor
4 years ago

Hi,

 

Hope 50 lines tell's enough

 

NummerGeopendOpgelost
INC001000224/01/2022 0:0024/01/2022 0:00
INC001000324/01/2022 0:0024/01/2022 0:00
INC001000624/01/2022 0:0024/01/2022 0:00
INC001000824/01/2022 0:00 
INC001001925/01/2022 0:0026/01/2022 0:00
INC001001225/01/2022 0:0025/01/2022 0:00
INC001002126/01/2022 0:0026/01/2022 0:00
INC001002426/01/2022 0:0026/01/2022 0:00
INC001002626/01/2022 0:00 
INC001003327/01/2022 0:00 
INC001004128/01/2022 0:003/02/2022 0:00
INC001003928/01/2022 0:0028/01/2022 0:00
INC001009631/01/2022 0:0018/02/2022 0:00
INC001009531/01/2022 0:0015/02/2022 0:00
INC001010031/01/2022 0:0015/02/2022 0:00
INC001005631/01/2022 0:0014/02/2022 0:00
INC001008431/01/2022 0:0014/02/2022 0:00
INC001009931/01/2022 0:0011/02/2022 0:00
INC001006731/01/2022 0:009/02/2022 0:00
INC001007331/01/2022 0:009/02/2022 0:00
INC001005731/01/2022 0:008/02/2022 0:00
INC001007231/01/2022 0:008/02/2022 0:00
INC001007931/01/2022 0:008/02/2022 0:00
INC001008831/01/2022 0:008/02/2022 0:00
INC001005831/01/2022 0:007/02/2022 0:00
INC001006431/01/2022 0:007/02/2022 0:00
INC001005531/01/2022 0:004/02/2022 0:00
INC001006531/01/2022 0:004/02/2022 0:00
INC001006931/01/2022 0:004/02/2022 0:00
INC001007731/01/2022 0:004/02/2022 0:00
INC001008931/01/2022 0:004/02/2022 0:00
INC001009231/01/2022 0:004/02/2022 0:00
INC001006031/01/2022 0:002/02/2022 0:00
INC001006631/01/2022 0:002/02/2022 0:00
INC001008331/01/2022 0:002/02/2022 0:00
INC001010431/01/2022 0:002/02/2022 0:00
INC001005031/01/2022 0:001/02/2022 0:00
INC001010331/01/2022 0:0031/01/2022 0:00
INC001004831/01/2022 0:00 
INC001004931/01/2022 0:00 
INC001005131/01/2022 0:00 
INC001005231/01/2022 0:00 
INC001005331/01/2022 0:00 
INC001005431/01/2022 0:00 
INC001005931/01/2022 0:00 
INC001006131/01/2022 0:00 
INC001006331/01/2022 0:00 
INC001006831/01/2022 0:00 
INC001007031/01/2022 0:00 
  • tamerj1's avatar
    tamerj1
    Community Champion
    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

    • baekelal's avatar
      baekelal
      Frequent Visitor
      4 years ago

      This works !!!

       

      Thanks alot.