Distinct Count for items listed planned but date column shows are Overdue

[Ankap]

Hi All,

I have been using PowerBi for a little while now and am familiar eith the basic functions - however I am funding it difficult to create the following.

I am looking to create a distinct count of items listed as 'planned' in the 'Interaction column' but have past there 'date of interaction' date:

Any help as to a dax forumula to enter into a custom column measure would be appreciated!

Regards

[mwaltercpa]

This DAX measure should allow  you to create a numeric flag for each item, using a 1 for overdue planned events. All other events will remain, but show a 0 in the column. The total in this example sums to 5 overdue planned events prior to todays date 7/8. 

OverdueInteraction =

VAR __t = ADDCOLUMNS(

SUMMARIZE(

FactTable,

DateTable[Date],

FactTable[Interaction Status]

),

"CTROW",

IF(AND(FactTable[Interaction Status]="Planned", DateTable[Date]<TODAY()),1,0)

)

return

SUMX(__t,[CTROW])

[mwaltercpa]

It appears you are trying to calculate this as a column rather than a measure. 

I'll also note, that when you create the measure, you'll want to adjust the table name (mine was FactSales) to show your table name that holds the events. 

If you are looking for a calcultaed column that counts all the overdue events regardless of current row, that is possible too, just let me know. 

Thanks, 
Mark

[Ankap]

Hi Thanks for your responce 🙂

when entering I am not allowed to proceed due to a 'Token EOF expected':

I am entering the formula as follows:

VAR __t = ADDCOLUMNS(
SUMMARIZE(
FactTable,
[Date Of Interaction],
[Interaction Status]
),
"CTROW",
IF(AND([Interaction Status]="Planned", [Date Of Interaction]<TODAY()),1,0)
)
return
SUMX(__t,[CTROW])

@mwaltercpa 

[bobbyilham]

Hi @Ankap 
Have you tried this formula:

CALCULATE ( DISTINCTCOUNT ( table1[Items ID] ) , table1[Interaction Status] = "Planned" , FILTER ( table1 , TODAY() > table1[Date of Interaction]) ) )

[Ankap]

Hi @bobbyilham 

When I tried that it is giving me the value '4' for all fields:

Could this be because non of the enteries are currently overdue?
I guess I am after the a '0' being put in the place of the enteries not being overdue and a '1' being in place where the entries are overdue..

Thanks!