topic Need help with counting a set of records that meet criteria of 2 dates comparison in DAX Commands and Tips

Need help with counting a set of records that meet criteria of 2 dates comparison

p-ha — Thu, 28 Apr 2022 22:06:25 GMT

Hello PowerBI experienced/super users:

I'm very new with PowerBI and am trying to work on a DAX to give me a proper count of records if it falls within the set criteria.

Couple facts:

- I'm working on a dataset that is built by IT and calculated columns function is not available. Must use DAX to create measures only.

- All data I have is on same table

- Invoice due date & paid date is [Date] data type

Objective: calculate/count all the invoices that incompliance and out of compliance based on the set criterias below (based on the comparison between paid date vs. due date)

Table [Invoice] sample data: In this sample, I'd have a count of 2 in compliance and 2 out of compliance.

Invoice id	Invoice Number	Due Date	Paid Date	Compliance
123	A123	5/28/2022		in compliance	In Compliance	count distinct invoice id if paid date < due date or (due date > today() and paid date = null)
15648	INV-563	3/28/2022	3/27/2022	in compliance	Out of compliance	count distinct invoice id if paid date > due date or (due date < today () and paid date = null)
78A656	7589-IN	2/22/2022	2/15/2022	in compliance
15647	INV-111	3/28/2022	4/1/2022	out of compliance
89	B899123	4/27/2022		out of compliance

Thank you very much for your time!! Any help is greatly appreciated.

P.Ha

Re: Need help with counting a set of records that meet criteria of 2 dates comparison

amitchandak — Fri, 29 Apr 2022 01:32:44 GMT

p-ha . Try measures

Calculate(DistinctCOUNT(Table[invoice id]), filter(Table,[paid date] < [due date] || ([due date] > today() && isblank([paid date]))))

Calculate(DistinctCOUNT(Table[invoice id]), filter(Table,[paid date] > [due date] || ([due date] < today() && isblank([paid date]))))

Re: Need help with counting a set of records that meet criteria of 2 dates comparison

p-ha — Fri, 29 Apr 2022 15:41:22 GMT

amitchandak thank you so much! the guided DAX worked!!

1 additional question please if you don't mind, what if my due date falls on the weekend? Is there anything we can add to the current DAX to caviat that if due date falls into weekend, the next earliest paid date is still consider on time? (e.g. due 4/30/2022 [Saturday], paid 5/2/2022 [earliest next Monday] --> on time payment, rather than the dax to compare just thes sequel of the date itself)

OR -

if we can incorporate into the measure to push the due date to the next business day if it falls in the weekend (since all the Paid date will defaulted to the business days only)

Thank you again!

P.Ha

Re: Need help with counting a set of records that meet criteria of 2 dates comparison

ddpl — Sat, 30 Apr 2022 11:50:43 GMT

Hi,

Please try this

Status =

IF(

NOT(ISBLANK('Table'[Paid Date]))&&('Table'[Due Date] > 'Table'[Paid Date]),

"Incompliance" ,

IF(

ISBLANK('Table'[Paid Date]) && 'Table'[Due Date] > TODAY(),

"Incompliance","Out of Compliance"

)

Re: Need help with counting a set of records that meet criteria of 2 dates comparison

ddpl — Mon, 02 May 2022 03:54:21 GMT

Hi,

Step 1 : First you should create weekday column from due date column:

weekday = WEEKDAY('Table'[Due Date])

Step 2: Create a New column of Due date which replace weekend to next working monday

Due Date N = IF('Table'[weekday]=7,'Table'[Due Date] +2,IF('Table'[weekday]=1,'Table'[Due Date]+1,'Table'[Due Date]))

Step 3 : Final result with 'Incompliance and out of compliance'

Status =

IF(

NOT(ISBLANK('Table'[Paid Date]))&&('Table'[Due Date N] > 'Table'[Paid Date]),

"Incompliance" ,

IF(

ISBLANK('Table'[Paid Date]) && 'Table'[Due Date N] > TODAY(),

"Incompliance","Out of Compliance"

)