topic How to write a measure that counts rows from 2 different dates columns in Power BI in DAX Commands and Tips

How to write a measure that counts rows from 2 different dates columns in Power BI

AAlvarez13 — Sun, 10 Mar 2024 17:38:33 GMT

I need to create a measure that counts rows based on 2 different date columns. Using 10/1/2023 as an reference example this should return 4 records. Where the Work Date is <= 10/1/2023 and the Paid Date is >= 10/1/2023
If I used 11/1/2023 it should return 5 records.
How would I translate this to DAX?

In Excel this is easily accomplished by adding a filter and then entering "Custom Date" for both Date columns.

Date Filters in Excel :

For example in excel the custom filter options: Work Date is before or equal to 10/1/2023 and Paid Date is after or equal to 10/1/2023

Adding a Custom Filter Excel :

The data model is using both active and inactive relationships.

How would I translate this to DAX?

Using 10/1/2023 as an reference example this should return 4 records.
Where the Work Date is <= 10/1/2023 and the Paid Date is >= 10/1/2023
If I used 11/1/2023 it should return 5 records.

Test Data :

OrderDate	OrderEndMonth	Forks	Napkins	Work Date	Paid Date
2/13/2023	2/1/2023	1	0
2/23/2023	2/1/2023	1	0
3/2/2023	3/1/2023	1	0	3/1/2023	12/6/2023
10/22/2019	10/1/2019	1	0	10/1/2019	11/26/2023
10/29/2019	10/1/2019	1	0
12/23/2019	12/1/2019	1	0	12/1/2019	10/22/2020
4/4/2023	4/1/2023	1	0
4/18/2023	4/1/2023	1	5	4/1/2023	6/22/2023
3/26/2019	3/1/2019	1	3	4/1/2019	8/30/2019
4/30/2019	4/1/2019	1	0
5/28/2019	5/1/2019	1	0
6/4/2019	6/1/2019	1	1	6/1/2019	7/15/2023
10/31/2023	10/1/2023	1	1	11/1/2023	12/14/2023
7/9/2019	7/1/2019	1	4	7/1/2019	8/21/2019
7/31/2018	7/1/2018	1	2	8/1/2018	10/10/2018
3/13/2018	3/1/2018	1	1
7/23/2019	7/1/2019	1	0	8/1/2019	9/24/2019
10/24/2019	10/1/2019	1	0	11/1/2019	12/11/2023
10/17/2019	10/1/2019	1	0
11/25/2019	11/1/2019	1	0
12/3/2019	12/1/2019	1	1	12/1/2019	11/15/2023
1/7/2020	1/1/2020	1	7	2/1/2020	2/28/2020
1/2/2020	1/1/2020	1	2	1/1/2020	2/18/2020

Expected Values:

Dates	Values
1/1/2023	4
2/1/2023	4
3/1/2023	5
4/1/2023	6
5/1/2023	6
6/1/2023	6
7/1/2023	5
8/1/2023	4
9/1/2023	4
10/1/2023	4
11/1/2023	5
12/1/2022	3

Here are the measures I have tried so far, the closest one is TestD. However, when i get to June of 2023 it returns the wrong count of June (5) should be 6 and October (5) should be 5.

Test1 = CALCULATE( COUNTROWS('TestData') ) TestA = CALCULATE( SUMX ( 'Date', CALCULATE ( COUNTROWS ( 'TestData' ), FILTER ( 'TestData', [Date] <= 'TestData'[Work Date] && [Date] <= 'TestData'[Paid Date] ) ) ) ) TestB = CALCULATE( COUNTROWS( FILTER('TestData', 'TestData'[Work Date] <= MAX('Date'[Date]) ) ) ) TestC = CALCULATE( COUNTROWS('TestData') ,'TestData'[Work Date] <= MAX('Date'[Date]) && 'TestData'[Paid Date] >= MAX('Date'[Date]) ) TestD = CALCULATE( COUNTROWS( FILTER( ALL('TestData'), 'TestData'[Work Date] <= MAX('Date'[Date]) && 'TestData'[Paid Date] >= MAX('Date'[Date]) ) ) ) TestG = COUNTROWS( FILTER( 'TestData', 'TestData'[Work Date] >= MIN('Date'[Date]) // date1 must be in the current month 1/2 && 'TestData'[Work Date]<= MAX('Date'[Date]) // date1 must be in the current month 2/2 && 'TestData'[Paid Date] >= MIN('Date'[Date]) // date 2 must be in the current month 1/2 && 'TestData'[Paid Date] <= MAX('Date'[Date] // date2 must be in the current month 2/2 )))

I'm really stuck on this one and have tried many things for a couple of days now. Any help would be greatly appreciated.
Added the Dropbox link to the pbix here SampleTestDates.PBIX

Re: How to write a measure that counts rows from 2 different dates columns in Power BI

HotChilli — Fri, 08 Mar 2024 23:45:07 GMT

Quick test with a disconnected date table. It looks like C and D are good.

Should this "count of June (5) should be 6 and October (5) should be 5." be
"count of June (5) should be 6 and October (5) should be 4."

Also the result data has a year 2022 entry in there

---

I couldn't access the attached files. If you fix that I'll take another look.

Re: How to write a measure that counts rows from 2 different dates columns in Power BI

AAlvarez13 — Sat, 09 Mar 2024 00:58:36 GMT

What is an easier way to drop a fileshare link or what is the most popular method? If i have to ill get dropbox which i dont currently have/use.

Re: How to write a measure that counts rows from 2 different dates columns in Power BI

HotChilli — Sat, 09 Mar 2024 11:29:35 GMT

The fileio was deleted.

The google drive wanted a login which I don't do.

Re: How to write a measure that counts rows from 2 different dates columns in Power BI

AAlvarez13 — Sun, 10 Mar 2024 17:39:30 GMT

I updated the link to the PBIX from dropbox. Adding it here as well SampleTestDates.pbix

Re: How to write a measure that counts rows from 2 different dates columns in Power BI

HotChilli — Mon, 11 Mar 2024 00:30:05 GMT

Thanks for pbix.

TestD is the one that works with a connected date table (as in your pbix).

So why do you think there is a problem (as illustrated on the 'Visual' tab)? The measure:

TestD = CALCULATE( COUNTROWS( FILTER(ALL('TestData'), 'TestData'[Work Date] <= MAX('Date'[Date]) && 'TestData'[Paid Date] >= MAX('Date'[Date]) ) ) )

works if you have a table visual with the 1st of each month and the measure.

It doesn't give you what you want on the 'Visual' tab because the month dimension includes all the days of the month (not just the first day) . So for the June 2023 example (desired: 6, actual:5) the record for work date: 4/1/2023, paid date: 06/22/2023 is not included in the count for the whole month because the paid date is before the end of June.

Here's a test measure that you can put in a visual with work date and paid date:

MeasureQ = var _theDate = DATE(2023,6,1) RETURN IF (AND(SELECTEDVALUE(TestData[Work Date]) < _theDate, SELECTEDVALUE(TestData[Paid Date]) > _theDate),1)

and you can identify which records are included in a 'month' visual or a 1st of Month visual.

Re: How to write a measure that counts rows from 2 different dates columns in Power BI

AAlvarez13 — Mon, 11 Mar 2024 20:16:37 GMT

What is happening is that this is first filtering the table on the Work Date and then
It filters that filtered table by the paid date. The problem is that I cant get that to work in a measure.
Heres what I tried.

DatesB = VAR xTable =
CALCULATETABLE(
'TestData', 'TestData'[Work Date] <= MAX('Date'[Date])
&& ISBLANK('TestData'[Work Date]) = FALSE()
)

VAR xCount = CALCULATE(
COUNTROWS(xTable)
,FILTER(xTable, 'TestData'[Paid Date] >= MAX('Date'[Date]) )
)
RETURN xCount