Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
=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.
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)))
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |