Date Count <= Today

[ajisharavind_99]

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

[amitchandak · Accepted solution]

@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()
)
)