Count calculation

[Ksm2004]

Hi all

I'm quite new to Power BI and currently stuck with row count based on slicer value.

I have Document table with main columns: CustomerID, Date Made and Date Finalised.

Date table was added as:

Date Finalised Table = CALENDAR(FIRSTDATE('document'[Date Finalised]),LASTDATE('document'[Date Finalised]))

On my page I have date range Slicer based on Date Finalised Table and Table visual.

I'd like to show finalised and not finalised document count:

Finalised: Date Finalised is not empty and is between Slicer range

Not Finalised: Date Finalised is empty or Date Finalised is later than Slicer max date.

In Table visual I'd like to see something like:

Docs Finalised           35

Docs Not Finalised    27

External filters will be applied to Document as well.

Please help 

Regards

[Ksm2004]

Please find a sample file:
https://drive.google.com/file/d/1Ei8dW8aCG7pyMN1NZA7I-y9dY5y232CG/view?usp=sharing

With Slicer range set 1-30/03/2024 and data as:

result should be:

Finalised: 4

Not Finaalised: 6

Thanks 

[_AAndrade · Accepted solution]

Hi @Ksm2004,

Here is my solution:

Finalised = COUNT(Document[Date Finalised])

Not Finalised = 
VAR _MaxDate = MAX('Date Finalised Table'[Date] )
VAR _AfterMaxDate= 
CALCULATE(
    [Finalised],
    FILTER(
        ALL(Document),
        Document[Date Finalised] >_MaxDate
        )
)
VAR _BlankDate =
    CALCULATE( COUNTBLANK(Document[Date Finalised]), ALL(Document))

VAR _Result = _AfterMaxDate + _BlankDate
RETURN
_Result

Final output:

[Ksm2004]

Thanks a lot! Works as expected.

Could you give some comments on the code please.

[_AAndrade]

@Ksm2004 

See if this comments can help you to understand the logic I'm using:

Not Finalised = 
VAR _MaxDate = MAX('Date Finalised Table'[Date] ) -- Find the Max Date of the context
VAR _AfterMaxDate= 
CALCULATE(
    [Finalised], -- Same as COUNT(Document[Date Finalised])
    FILTER(
        ALL(Document), -- Remove all filter on this table
        Document[Date Finalised] >_MaxDate -- Give me only the rows where date finalised is bigger than the max date of the slicer
        )
)
VAR _BlankDate =
    CALCULATE( COUNTBLANK(Document[Date Finalised]), ALL(Document)) -- count all blank rows of the column "Date Finalised" and do this calculation of all rows of the document table (I'm using again ALL function to remove any filter)

VAR _Result = _AfterMaxDate + _BlankDate
RETURN
_Result

[Ksm2004]

Thanks for the explanation!

I'm trying to transfer measures to a real project. It's using dataverse as a backend and Direct Query model.

If I try to slide the Slicer, all values (Finalised and Not Finalised )  becomes blank.

Any advice on how to address it please.

[_AAndrade]

Hi @Ksm2004,

Can you please provide a sample of your data, for example two table with some data, and the final output?