Forum Discussion

ajisharavind_99's avatar
ajisharavind_99
Icon for Advocate I rankAdvocate 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...
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()
    )
    )

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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()
)
)