Creation of measure according to date

[Syndicate_Admin]

Hello Community, I have a question and I do not know how to make the query, but I will try to be clear ...

I have a table with several columns (management, area, company, description of the action to be performed, status of the share (late, on time, etc.) and the expiration date of the share).

What I need is to generate a measure that allows me to obtain the number of all corrective actions with delayed action status that are more than 15 days late.

How can I do it?

Thank you

[amitchandak · Accepted solution]

@Syndicate_Admin , There should be two days, assume the second date is today. You need a measure

Calculate(countrows(Table), filter(Table, Table[Status] = "Late" && Table[expiration date] <Today() -15 ))

[v-jayw-msft · Accepted solution]

Hi @Syndicate_Admin ,

Something like below:

measure = Calculate(COUNT([ACTION]),FILTER('TABLE',[STATUS]="LATE"&&[DELAYED DATE]>15))

If you don't have the [DELAYED DATE] in the table, you could use DATEDIFF() function to get the day difference between [expiration date] and TODAY() or any given date.

Modify the formula as below:

measure = 
VAR DELAYED DATE = DATEDIFF([expiration date],TODAY(),DAY)
RETURN

Calculate(COUNT([ACTION]),FILTER('TABLE',[STATUS]="LATE"&&[DELAYED DATE]>15))

Best Regards,

Jay

[amitchandak · Accepted solution]

@Syndicate_Admin , There should be two days, assume the second date is today. You need a measure

Calculate(countrows(Table), filter(Table, Table[Status] = "Late" && Table[expiration date] <Today() -15 ))