Forum Discussion

wolfsvein's avatar
wolfsvein
Regular Visitor
8 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

    • wolfsvein's avatar
      wolfsvein
      Regular Visitor
      8 years ago
      The SQL example show what the date example and I am putting in a measure.
    • wolfsvein's avatar
      wolfsvein
      Regular Visitor
      8 years ago
      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.

  • PietroFarias's avatar
    PietroFarias
    Resolver II
    8 years ago

    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

    • wolfsvein's avatar
      wolfsvein
      Regular Visitor
      8 years ago

      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)))