Dax help

[sameergupta60]

how to get below excel formula in power bi.

=COUNTIFS('Milestone sheet'!$B$2:$B$8107,"="&A9,'Milestone sheet'!$L$2:$L$8107,">=01/07/20",'Milestone sheet'!$L$2:$L$8107,"<=20/09/20")-C9

Please help us.

[AntrikshSharma]

Try something like this:

=
VAR StartDate =
    DATE ( 2020, 07, 01 )
VAR EndDate =
    DATE ( 2020, 09, 20 )
VAR RowsSatisfyingCondition =
    FILTER (
        ALLSELECTED ( 'Milestone sheet' ),
        'Milestone sheet'[Column] = "Something"
            && 'Milestone sheet'[Date Column] >= StartDate
            && 'Milestone sheet'[Date Column] <= EndDate
    )
VAR Result =
    COUNTROWS ( RowsSatisfyingCondition )
        - SUM ( Table[TheColumnThatRepresents C9] )
RETURN
    Result

[amitchandak]

@sameergupta60 , difficult to tell with excel formula .Can you share sample data and sample output in table format?

[sameergupta60]

hi,

we have an actual finish date column from that we extract the date qtr wise if the date between  01/07/20 to 20/09/30 comes in the green count.

if the date between 21/09/20 to 30/09/20 comes in amber count.

[amitchandak]

@sameergupta60 , there are functions like startofquarter and endofquarter but they need continuous dates. So need to join it with date table and get these dates .

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.