Count rows when the date is in between dates in a related table

[skelecopter]

I'm essentially trying to calculate the number of rows in a table that have a date in the previous month. Each row in my date lookup table has a column for the start and end dates of that month.

I would think I could do:

CALCULATE(
COUNTROWS(FactTable),
FILTER(FactTable,
FactTable[Date] >= RELATED(DateTable[StartOfLastMonth]) &&
FactTable[Date] <=RELATED(DateTable[EndOfLastMonth))

But I see that I am unable to use RELATED in this way. However, specifying a literal date to compare against rather than a column value does not work either, whether in a measure or calculated aggregation column in a third related table.

What I am getting wrong thinking about this? I have tried to DATEADD but was getting no results with that either.

[littlemojopuppy]

Hi @skelecopter.  DAX acts kind of weird when it comes to dates.  With the sample you provided

CALCULATE(
	COUNTROWS(FactTable),
	FILTER(
		FactTable,
		FactTable[Date] = 01/19/2022
	)
)

It could be doing a couple of things...  First, dates are really just the count of whole numbers since a given date (I think January 1, 1900, but not positive).  So by passing 1/19/2022 into it, you could be passing 1 divided by 19 divided by 2022.  Which would be a really small decimal, which would be interpreted as a few seconds after midnight on Jan 1, 1900.

Additionally, there's the problem of is it MM/DD/YYYY format or DD/MM/YYYY format.  It's obvious that 1/19/2022 is January 19, 2022 because there are only 12 months.  But what if the date was 1/7/2022?  Is that January 7, 2022 or July 1, 2022?

What I would do if I wanted to filter for a specific date is this

CALCULATE(
	COUNTROWS(FactTable),
	FILTER(
		FactTable,
		FactTable[Date] = DATE(2022, 1, 19)
	)
)

 Sorry I hadn't had a chance to reply earlier.  Been crazy busy since the minute I got to work this morning.

Hope this rambling explanation helps a little...

[skelecopter]

Thank you very much! Your first solution worked exactly as expected. I will spend some time trying to understand why. 🙂

[littlemojopuppy]

@skelecopter you're welcome...glad I could help.  (and I tried to answer your earlier question at the bottom of the thread)

[skelecopter]

Thank you so much again! DATE is what I was after and was making a bad assumption that it would work like that as long as the date fields were properly typed.

I realized I had asked for the wrong thing in my example expression (the sum of last month and this month), but DATESINBETWEEN is what I was really needing!

[littlemojopuppy · Accepted solution]

Hi @skelecopter 

I'm assuming "date in the previous month" means as of today?  If so, try this

CALCULATE(
	COUNTROWS(FactTable),
	FILTER(
		DateTable,
		DateTable[Date] > EOMONTH(TODAY(),-2) + 1 &&
		DateTable[Date] <= EOMONTH(TODAY(), -1)
	)
)

Hope this helps!

[skelecopter]

I will try it! Could you give me a hint as to why using a date literal/DATEADD doesn't work there?

I noticed that it DID work if I gave FILTER a string (e.g., FILTER where a column with names = "Someone's Name").

[littlemojopuppy]

Hi @skelecopter.  That's hard to answer without having access to the data model.  I remember I was going to PS my response with "there are probably more fancy ways to do this, depending on what you're really trying to do"  😉

There are actually several ways this could be done, depending on what you're really trying to get to and how you're going to use it in a report.  I offered you what's probably the simplest.  The original solution and the following one both literally looking at "last month" defined (as I type this on January 19, 2022) being December 2021.  Assuming you have a date table and it's marked appropriately, an equivalent to what I offered is this

CALCULATE(
	COUNTROWS(FactTable),
	DATESBETWEEN(
		DateTable[Date],
		EOMONTH(TODAY(),-2) + 1,
		EOMONTH(TODAY(), -1
	)
)

A similar thing could be done with either of these

CALCULATE(
	COUNTROWS(FactTable),
	DATESINPERIOD(
		DateTable[Date],
		EOMONTH(TODAY(),-2) + 1,
		1,
		MONTH
	)
)

CALCULATE(
	COUNTROWS(FactTable),
	PREVIOUSMONTH(DateTable[Date]
)

A possible interpretation of your ask is "in the month leading up to the date in the fact table".  That gets a little more complex.  So the question is what are you really trying to do (in plain, simple language) so we can figure this out?  🙂

[skelecopter]

Yeah, nothing fancy, and my data model is just the fact table (sales) and three lookup tables (sales people and locations, plus the date able). I've checked and triple checked that they're all connected correctly with correct data types.

My question, looking at your solutions, is more about how the FILTER function works then.

I mentioned that I couldn't even pass a date literal in my example to say "return the records where the date = 01/19/2022". What would I do in that scenario? The below did not work, even though entries exist for that date (and did not work either when I tried without using FILTER and just passing the filter expression within CALCULATE):

CALCULATE(
	COUNTROWS(FactTable),
	FILTER(
		FactTable,
		FactTable[Date] = 01/19/2022
	)
)

The following DOES however work:

CALCULATE(
	COUNTROWS(FactTable),
	FILTER(
		FactTable,
		FactTable[SalesPerson] = "John Smith"
	)
)

And what if I DID need to reference a foreign column there? I have practiced using RELATED with aggregation/iterator functions; does it not work outside of them?