DAX help with calculation of a virtual table and a virtual calculated column

[Anonymous]

Hello,

I am trying to create a table that will help me calculate how many customers had orders in all previous 4 quarters from a selected date (e.g. if we choose a date from Q3, I need the customers that had orders in Q3, Q2, Q1 of selected year + Q4 of previous year). The logic that I use is that if the customer has orders in all 4 consecutive quarters, summing up those quarter numbers will always be 10 (Q (2+3+4+1)).

This is the dax that I wrote so far:

Virtual Table =

var maxdate = max(table[data])

var table1 =

Filter(

ADDCOLUMNS(

summarize(

table,

[user_id],

[data]),

"@quarter",

quarter(table[data])

),

[data]>date(2020,11,1) -- this date() is dynamic and will need to search only in the last 12 months (4 quarters)

&& not ISBLANK([user_id])

)

var table2 =

ADDCOLUMNS(

table1,

"@Q sum/user",                                  ---- this should return the quarter number and should sum it up for every user ID

calculate(sumx(table1,[@quarter]),

filter(

table1,

[user_id]<= earlier ([user_id])

)

)

)

return table2

------------------------

The issue is that the column "@Q sum/user" is summing up everything from that column and does not sum up the quarters per user.

-----------------------

The correct calculation for :"@Q sum/user" is given by the below calculated column.

@ quarter sum/user =

calculate(

sum([@quarter]),

ALLEXCEPT('virtual table','virtual table'[user_id])

)

How to include the calculated column "@ quarter sum/user" directly in the virtual table?

Also, can you help me with the virtual table as well? The way it is written is returning all dates when the customer made a purchase. I only need to look at the last 4 calendar quarters.

If you have another suggestion of how to get to the same result, please share. 

Thank you for your help!!

[OwenAuger · Accepted solution]

Hi @Anonymous 

Interesting problem 🙂

I think this can be written a bit more simply.

First of all, you really should have a Date table related to your fact table, with appropriate columns (such as identifiers of "Year Quarter" etc). This will help with any date-related DAX calculations.

You should also have tables for dimensions such as Customer.

Assuming you do have your model set up like this, here is a sample DAX query on dax.do, illustrating how you can create a table containing Customers and the number of quarters (of the last 4) in which they had orders.

https://dax.do/hvx5e2dLxpBdMN/

In the above query, I defined a measure that constructs a table CustomersQuartersCount, which contains Customer[CustomerKey] and [@NumQuarters]. The DAX required to produce this table is:

VAR CustomersQuarters =
	CALCULATETABLE (
		SUMMARIZE ( Sales, Customer[CustomerKey], 'Date'[Calendar Year Quarter Number] ),
		-- Year ending in quarter of max date
		DATESINPERIOD (
			'Date'[Date],
			ENDOFQUARTER ( 'Date'[Date] ),
			-1,
			YEAR
		)
	)
VAR CustomersQuartersCount =
	GROUPBY (
		CustomersQuarters,
		Customer[CustomerKey],
		"@NumQuarters", SUMX ( CURRENTGROUP (), 1 )
	)

The quarters that are included in the above formula are the calendar quarters up to and including the quarter of the maximum date in the filter context.

I'm hoping you can adapt the above to your model, provided you first create a Date table (containing a column equivalent to 'Date'[Calendar Year Quarter Number]), and ideally a Customer table.

The Customer table may not be strictly needed for this to work however, and you could probably use your existing user_id column.

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @Anonymous 

Interesting problem 🙂

I think this can be written a bit more simply.

First of all, you really should have a Date table related to your fact table, with appropriate columns (such as identifiers of "Year Quarter" etc). This will help with any date-related DAX calculations.

You should also have tables for dimensions such as Customer.

Assuming you do have your model set up like this, here is a sample DAX query on dax.do, illustrating how you can create a table containing Customers and the number of quarters (of the last 4) in which they had orders.

https://dax.do/hvx5e2dLxpBdMN/

In the above query, I defined a measure that constructs a table CustomersQuartersCount, which contains Customer[CustomerKey] and [@NumQuarters]. The DAX required to produce this table is:

VAR CustomersQuarters =
	CALCULATETABLE (
		SUMMARIZE ( Sales, Customer[CustomerKey], 'Date'[Calendar Year Quarter Number] ),
		-- Year ending in quarter of max date
		DATESINPERIOD (
			'Date'[Date],
			ENDOFQUARTER ( 'Date'[Date] ),
			-1,
			YEAR
		)
	)
VAR CustomersQuartersCount =
	GROUPBY (
		CustomersQuarters,
		Customer[CustomerKey],
		"@NumQuarters", SUMX ( CURRENTGROUP (), 1 )
	)

The quarters that are included in the above formula are the calendar quarters up to and including the quarter of the maximum date in the filter context.

I'm hoping you can adapt the above to your model, provided you first create a Date table (containing a column equivalent to 'Date'[Calendar Year Quarter Number]), and ideally a Customer table.

The Customer table may not be strictly needed for this to work however, and you could probably use your existing user_id column.

Regards,

Owen

[Anonymous]

Hi Owen,

I just noticed that the dax partially works. When I change the month from September to August (so in the same Q), the result is not modified. Only when I select June (so when the Q changes) the result is updated.

Can you please help me modify the formula so that it considers the selected month as the end of quarter and calculate the rest from there?

[Anonymous]

What I tried is to replace the ENDOFQUARTER with EOMONTH, but it didn't work

[OwenAuger · Accepted solution]

Hi again @Anonymous 

That's an interesting twist, and I had wondered if you would want that.

We need to modify the DAX to:

1. First summarize by Customer & Month
2. Then convert each month to a "Quarter Index", which is defined relative to the maximum month visible in the filter context.
3. Then we summarize by Customer and Quarter Index

I have created an updated example on dax.do:

https://dax.do/jBqqJlQ1utguHa/z8oJpdk/

The key excerpt from the code is:

VAR MaxDate = 
	MAX ( 'Date'[Date] )
VAR CustomersQuarters =
	CALCULATETABLE (
		VAR CustomersMonths =
			SUMMARIZE ( Sales, Customer[CustomerKey], 'Date'[Calendar Year Month] )
		VAR CustomersQuarterIndex =    
			SELECTCOLUMNS (
				CustomersMonths,
				"CustomerKey", Customer[CustomerKey],
				"QuarterIndex",
				QUOTIENT ( DATEDIFF ( CALCULATE ( MAX ( 'Date'[Date] ) ), MaxDate, MONTH ), 3 )
			)
		VAR CustomersQuarterIndexSummarized =
			SUMMARIZE (
				CustomersQuarterIndex,
				[CustomerKey],
				[QuarterIndex]
			)
		RETURN
			CustomersQuarterIndex,
		-- Year ending in month of max date
		DATESINPERIOD (
			'Date'[Date],
			EOMONTH ( MaxDate, 0 ),
			-1,
			YEAR
		)
	)
VAR CustomersQuartersCount =
	GROUPBY (
		CustomersQuarters,
		[CustomerKey],
		"@NumQuarters", SUMX ( CURRENTGROUP (), 1 )
	)

At least that's what I can think of for now 🙂

Regards,

Owen

[Anonymous]

OK, you are more advanced in coding than I am (obviously), but it's an excellent exercise. 

BTW, if you ever visit Romania, please write 🙂 I want to return a favour.

What I have done meanwhile is to write the below code. It's not as pretty as yours, but it does the job. I know that there are some virtual tables that can be joined, but I'll work on that later.

Thank you for all your effort, Owen!

# retained users =

var MaxDate = selectedvalue('Calendar '[Month Year])

var ActiveFirstQ =

CALCULATETABLE(

summarize(

TABLE,[Month Year]),

all('Calendar'[Month Year]),

DATESBETWEEN(TABLE[data],edate(MaxDate,-2),MaxDate))

var ActiveSecQ =

CALCULATETABLE(

summarize(

TABLE,[Month Year]),

all('Calendar'[Month Year]),

DATESBETWEEN(TABLE[data],edate(MaxDate,-5),edate(MaxDate,-3)))

var ActiveThirdQ =

CALCULATETABLE(

summarize(

TABLE,[Month Year]),

all('Calendar'[Month Year]),

DATESBETWEEN(TABLE[data],edate(MaxDate,-8),edate(MaxDate,-6)))

var ActiveFourthQ =

CALCULATETABLE(

summarize(

TABLE,[Month Year]),

all('Calendar'[Month Year]),

DATESBETWEEN(TABLE[data],edate(MaxDate,-11),edate(MaxDate,-9)))

var table1 =

ADDCOLUMNS(

FILTER(

CALCULATETABLE(

summarize(

TABLE,

[user_id],

[Month Year],

[tip_activitate]

),

all(TABLE)),

not ISBLANK([user_id]) && [tip_activitate] in {"X","Y"}

),

"FirstQ",

switch(TRUE(),

[Month Year] in ActiveFirstQ,1,0),

"SecondQ",

switch(TRUE(),

[Month Year] in ActiveSecQ,1,0),

"ThirdQ",

switch(TRUE(),

[Month Year] in ActiveThirdQ,1,0),

"FourthQ",

switch(TRUE(),

[Month Year] in ActiveFourthQ,1,0)

)

var table2 =

SUMMARIZE(

table1,

[user_id],

[FirstQ],

[SecondQ],

[ThirdQ],

[FourthQ],

"Add",

[FirstQ]+[SecondQ]+[ThirdQ]+[FourthQ])

var table3 =

FILTER(

ADDCOLUMNS(

summarize(

table2,

[user_id]),

"@total",

sumx(

filter(

table2,

[user_id]=earlier([user_id])),

[Add]))

,

[@total]=4)

return

countrows(table3)

[Anonymous]

Thank you very much, Owen. The calculation worked.

Yes, I do have a dedicated date table. And you are right. I can't explain why I didn't use that in my code.

I may not fully understand is the reasoning behind the calculation, maybe you can help me better understand please?

So the CustomersQuarters is creating a virtual table that contains CustomerKey (which is the customer ID) and Year Quarter Number and filters it to only show the past year from the selected date, right? Basically here we have a table with all the customers that purchased in the past 12 months, by quarter.

How does CustomersQuartersCount work?

[OwenAuger]

Glad it helped 🙂

Yes that's right, CustomersQuarters is a virtual table with two columns (Customer & Quarter), containing all combinations of Customers and the Quarters in which they purchased, over the previous 4 quarters (due to the DATESINPERIOD(...) date filter).

So it will look like:

Customer	Year-Quarter
Cust1	2021-Q1
Cust1	2021-Q2
Cust2	2021-Q3

etc.

Then CustomersQuartersCount uses GROUPBY to group CustomersQuarters by Customer, with a column @NumQuarters which counts the number of rows for each Customer (which is equal to the number of quarters). I used SUMX but COUNTX would also work.

Note: GROUPBY is similar to SUMMARIZE but must follow specific rules, and can use iterators for computed columns with the CURRENTGROUP() argument.

This table will then have one row per Customer who purchased over the previous 4 quarters, and will look like:

Customer	@NumQuarters
Cust1	2
Cust2	1
Cust3	4

etc.

You can then do what you need to with this table, e.g. filter on @NumQuarters = 4 and count the rows as I did in my dax.do example.

Hopefully that was clear but let me know if I can help further!

Owen

[Anonymous]

This is really helpful!

It's the first time I'm seeing GROUPBY in use. If we were to rewrite this expression using SUMMARIZE, do you mind showing me how would it look like?

[OwenAuger]

That's great!

Actually, in this case SUMMARIZE won't work for CustomersQuartersCount, because SUMMARIZE cannot make use of CURRENTGROUP() to count the rows within a group of CustomersQuarters.

An alternative would be to compute @NumQuarters in a single step by adding an extension column to the original SUMMARIZEd table using ADDCOLUMNS:

VAR CustomersQuartersCount =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Sales, Customer[CustomerKey] ),
            "@NumQuarters",
                -- Optimized calculation of "related distinct count"
                -- https://www.daxpatterns.com/related-distinct-count/
                CALCULATE (
                    SUMX ( SUMMARIZE ( Sales, 'Date'[Calendar Year Quarter Number] ), 1 )
                )
        ),
        -- Year ending in quarter of max date
        DATESINPERIOD (
            'Date'[Date],
            ENDOFQUARTER ( 'Date'[Date] ),
            -1,
            YEAR
        )
    )

Note that SUMMARIZE can create extension columns, but ADDCOLUMNS/SUMMARIZE is preferred:

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

All the best,

Owen

[Anonymous]

Thank you for your time and explanation, Owen. It really helped a lot!