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 a...
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:

     

  • _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.

      • _AAndrade's avatar
        _AAndrade
        Resident Rockstar
        2 years ago

        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