count rows on condition only for last date

[stephanel]

Hi,

I'm beginner in DAX. I've got a dataset like product, date, completed (a boolean value True/false), amount. I need to get the count of not completed rows where completed is false only for the last date by products.

product, date, completed
product1, 2023-12-01, false
product2, 2023-12-01, false
product3, 2023-12-01, true
product4, 2023-12-01, false
product3, 2023-12-02, true
product3, 2023-12-03, true
product2, 2023-12-03, false
product4, 2023-12-04, true
product3, 2023-12-04, false
product3, 2023-12-05, false

At 2023-12-05 (or after if no more data are added) I have

product1, 2023-12-01, false
product2, 2023-12-03, false
product3, 2023-12-05, false
product4, 2023-12-04, true

 My goal is to have a count of the completed_0 and completed_1. something like :

count_completed = 1 and count_not_completed = 3.

And when visualising by product, I like to have

product1 : 1 not completed, 0 completed
product2 : 1 not completed, 0 completed
product3: 1 not completed, 0 completed
product4 : 0 not completed, 1 completed

When changing the global filter on date to go back in time, the "last date" should change the results according to the selection.

I tried many approach using CALCULATE, LASTNONBLANKVALUE ... without result.

Is a DAX approach is possible to answer this question ? what measure should I use to get the result I need ?

Thanks for your help.

Stéphane

[stephanel]

Hi thanks for your answer.

I just need a simple count (not a string with "X completed"), juste avec X as integer is enough.

The count should only be done on the last entry by product and these entries exists only in "stock" style : one first entry by product at time t (when the data are inserted in source database), then another entry for same product at t+some days (when the entry was updated in the source database because an attribute was changed).

What I'm trying to do is having a dashboard giving me :

• two cards giving the total of completed / not completed (all product included). This means, to take only the last completed value for each product (in regards to the date filter in the page). More simply said : the last version for each product.
• a graph on a category (which I didn't show on the data , but each products are grouped by category) saying for category A there is 45 products completed and 55 products not completed, meaning only taking account of the last completed values by product in regards to the date filter in the page. More simply said : the last version for each product.

The main point is to always calculate the number of completed / not completed based on the last version visible in the actual date context for each product.

For exemple, in my above dataset, for product3, we have 5 versions on 05-12-2023.

I my vision is at 05-12-2023 (or later supposing there is no new version for product3), the completed value for product3 is false (the value on 05-12-2023 version).

If I change the date back in time to 01-12-2023, I'll see the version on 01-12-2023 and completed is true.

For product4, which have only 2 version (on 01-12-2023 and 04-12-2023), if I'm positioned on date 05-12-2023, I'll see the last version which is on 04-12-2013.

So, I I'm positionned on 05-12-2023 my dashboard will show something like

[completed car : 1 ] [non completed card : 1] [ graph by category with category A : 1 completed, 0 not completed  Category B : 0 completed, 2 not completed, and so on]

I'll try to have an small dataset of values.

Thanks for your help.

[Anonymous]

Hi, @stephanel 

May I ask if this is the expected output you are looking for? Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

Completed status =

VAR _date =

    SELECTEDVALUE ( 'Table'[Date] )

VAR _completed_date =

    SELECTEDVALUE ( 'Table'[Completed date] )

RETURN

    IF ( _completed_date >= _date, "false", "true" )



TrueCounts =

VAR _count_true =

    CALCULATE (

        COUNT ( 'Table'[Product] ),

        FILTER ( 'Table', 'Table'[Completed status] = "true" )

    )

RETURN

    IF (

        NOT ISBLANK ( _count_true ),

        _count_true & "  completed",

        0 & "  completed"

    )



FalseCounts =

VAR _count_false =

    CALCULATE (

        COUNT ( 'Table'[Product] ),

        FILTER ( 'Table', 'Table'[Completed status] = "false" )

    )

RETURN

    IF (

        NOT ISBLANK ( _count_false ),

        _count_false & "   not completed",

        0 & "   not completed"

    )

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[saurabhtd]

@stephanel You can try these DAX logics. These will give you desired results.
 

Count of Not Completed Rows =
CALCULATE(
COUNTROWS(Table),
FILTER(
Table,
Table[Completed] = FALSE
&& Table[Date] = MAX(Table[Date])
)
)

Count of Completed Rows=
CALCULATE(
COUNTROWS(Table),
FILTER(
Table,
Table[Completed] = TRUE
&& Table[Date] = MAX(Table[Date])
)
)

if you want to visualise by product, you can use a matrix visual and place the 'Product' field in rows, and then use the measures 'Count of Not Completed Rows' and 'Count of Completed Rows' in values.