Forum Discussion

ajisharavind_99's avatar
ajisharavind_99
Advocate I
4 years ago
Solved

Date Count <= Today

Hi, 

I am stuck in the below calculation , When I apply filter <= today it also counting blank
I need below result when i select a year (i.e 2021) in Slicer (Calendar Table)

 

 

Task Plan as of today =
CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Planned] ),
    FILTER(
        Sheet1,
        Sheet1[Task Planned]
        <= TODAY()
    )
)

Task completed as of today =
CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Completed] ),
    FILTER(
        Sheet1,
        Sheet1[Task Completed]
        <= TODAY()
    )
)

 

Data set and pbix available in below link

https://drive.google.com/drive/folders/14JD2NuIVIZn7Muuem8KyPLxoEWVexzjO?usp=sharing

needhelp
  • amitchandak's avatar
    amitchandak
    4 years ago

    ajisharavind_99 , Try like

     

    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Planned] )),
    FILTER(
    Dim_Calendar,
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

    or

    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Planned] )),
    FILTER(
    all(Dim_Calendar),
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

    ----------------


    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Completed] )),
    FILTER(
    Dim_Calendar,
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

    or

    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Completed] )),
    FILTER(
    all(Dim_Calendar),
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ajisharavind_99 , Try like

     

    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Planned] )),
    FILTER(
    Dim_Calendar,
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

    or

    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Planned] )),
    FILTER(
    all(Dim_Calendar),
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

    ----------------


    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Completed] )),
    FILTER(
    Dim_Calendar,
    Dim_Calendar[Date]
    <= TODAY()
    )
    )

    or

    CALCULATE(CALCULATE(
    COUNTROWS( Sheet1 ),
    USERELATIONSHIP( Dim_Calendar[Date] , Sheet1[Task Completed] )),
    FILTER(
    all(Dim_Calendar),
    Dim_Calendar[Date]
    <= TODAY()
    )
    )