cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Help Need DAX row count between dates

=CALCULATE(COUNTROWS(Fact_SalesSummaries),
     DATESINPERIOD(Fact_SalesSummaries[BidWonDate],
         LASTDATE(Fact_SalesSummaries[BidWonDate]),
         -3, MONTH),
     Fact_SalesSummaries[BidWonDate] <> BLANK())

 

I have tried search and different ways to counts rows that have dates between 2 different dates it calculates the rows as 1 and not more than that in the formula.

This is meant to be a rolling 3-month count of records from date back 3 months to be able to calculate an average with a count of all records.

Example in SQL would look like this.

select MonthBlended, yearblended, BidWonDate, BidWonFlag,
(select count(*) from Fact_SalesSummaries
	where convert(date, cast(MonthBlended as varchar(2))+ '/01/' + cast(YearBlended as varchar(4)), 101) between 
	dateadd(mm, -3, convert(date, cast(t1.MonthBlended as varchar(2))+ '/01/' + cast(t1.YearBlended as varchar(4)), 101))
	and convert(date, cast(t1.MonthBlended as varchar(2))+ '/01/' + cast(t1.YearBlended as varchar(4)), 101)
	and BidWonDate is not null
	) as CountWONPrior3Month,
(select count(*) from Fact_SalesSummaries
	where convert(date, cast(MonthBlended as varchar(2))+ '/01/' + cast(YearBlended as varchar(4)), 101) between 
	dateadd(mm, -3, convert(date, cast(t1.MonthBlended as varchar(2))+ '/01/' + cast(t1.YearBlended as varchar(4)), 101))
	and convert(date, cast(t1.MonthBlended as varchar(2))+ '/01/' + cast(t1.YearBlended as varchar(4)), 101)
	) as CountALLPrior3Month
 from Fact_SalesSummaries t1

 

BidWonDate                BidWon  CountWonPrior3Month CountAllPrior3Month
-----------------------   ------  ------------------- -------------------
2014-07-17 00:00:00.000	  1	      618	                1048
2014-07-17 00:00:00.000	  1	      618	                1048
2014-07-17 00:00:00.000	  1	      618	                1048
2014-07-17 00:00:00.000	  1	      618	                1048
NULL	                    0	      618	                1048
NULL	                    0	      618	                1048
NULL	                    0	      618	                1048
NULL	                    0	      618	                1048
2014-07-17 00:00:00.000	  1	      618	                1048
NULL	                    0	      618	                1048
NULL	                    0	      618	                1048
2014-07-11 00:00:00.000	  1	      618	                1048

I am trying to do this in a tabular model in SSAS I have tried with a calendar and without a calendar table but everything I have read says this should work but the issue is that they alway calculate to 1 and not a some of prior 2 months and current month.

 

 

5 REPLIES 5
Resolver II
Resolver II

I'm not good with SQL, so i want understand what you need.
Do you need retrieve the number of rows where:
1. Start 03 month back and finish the last date previous the current date.
2. BidWonDate <> Blank.

Yeah? Or did I change everything? haha

SO what is needed is a rolling 3 month count of records between prior 3 months and current date, and the records are not blank().

 

I even tried does this in a measure.

 

calculate(countrows(date), datesbetwween(date[field], eomonth(lastdate(date[field]),-4)+1, eomonth(lastdate[date[field],0)))
Microsoft
Microsoft

Hi @wolfsvein,

 

Can you share a sample please? You can mask the data first. 

It seems the formula is a calculated column. How about using it as a measure?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

DECLARE @lower INT = 1
	,@upper INT = 6
	,@randnum INT
	,@date DATE = getdate()
	,@endofcurrentmonth DATE
	,@loopdate DATE
	,@loop INT
	,@loopmax INT
	,@innerloop INT
	,@innerloopmax INT
	,@value DATE

IF object_id('tempdb..#tmp_DateRange') IS NOT NULL
	DROP TABLE #tmp_DateRange

CREATE TABLE #tmp_DateRange (
	ID INT identity(1, 1) PRIMARY KEY NOT NULL
	,[Date] DATE
	)

IF object_id('tempdb..#tmp_RandomData') IS NOT NULL
	DROP TABLE #tmp_RandomData

CREATE TABLE #tmp_RandomData (
	ID INT identity(1, 1) PRIMARY KEY NOT NULL
	,[BidDateWon] DATE
	,[BidWon] BIT
	)

SELECT @endofcurrentmonth = dateadd(mm, datediff(MM, 0, dateadd(MM, 1, @date)), 0) - 1

SELECT @loopdate = dateadd(mm, - 9, dateadd(dd, 1 - datepart(dd, @date), @date))

WHILE @loopdate <= @endofcurrentmonth
BEGIN
	INSERT INTO #tmp_DateRange ([Date])
	VALUES (@loopdate)

	SET @loopdate = dateadd(dd, 1, @loopdate)
END

SELECT @loop = 0
	,@loopmax = max(ID)
FROM #tmp_DateRange

WHILE @loop < @loopmax
BEGIN
	SELECT @randnum = round(((@upper - @lower - 1) * rand() + @lower), 0)

	SET @loop = @loop + 1
	SET @innerloopmax = @randnum
	SET @innerloop = 0

	IF @randnum % 2 = 0
	BEGIN
		SET @value = (
				SELECT [date]
				FROM #tmp_DateRange
				WHERE ID = @loop
				)
	END
	ELSE
	BEGIN
		SET @value = NULL
	END

	WHILE @innerloop <= @innerloopmax
	BEGIN
		SET @innerloop = @innerloop + 1

		INSERT INTO #tmp_RandomData (BidDateWon)
		VALUES (@value)
	END
END

UPDATE #tmp_RandomData
SET BidWon = 1
WHERE BidDateWon IS NOT NULL

SELECT *
FROM #tmp_RandomData

I created this random data generated SQL so it give you a sample data set to work with. Run this in TSQL and create a physical table then you can import in Excel or PowerBI to work with the DAX syntax.

The SQL example show what the date example and I am putting in a measure.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors