Forum Discussion

Ksm2004's avatar
Ksm2004
New Member
2 years ago
Solved

Count calculation

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

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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:

     

6 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi Ksm2004,

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

    • _AAndrade's avatar
      _AAndrade
      Resident Rockstar
      2 years ago

      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's avatar
        Ksm2004
        New Member
        2 years ago

        Thanks a lot! Works as expected.

         

        Could you give some comments on the code please.