DAX: Index number based on two columns

[biz_wiz]

Hi,

I'm still learning DAX and would really appreciate some support on creating an index number in DAX based on two columns in the data.

Data looks like this:

TransactionDate	Customer ID	Product
1.2.2019	A	X
5.2.2019	A	X
16.2.2019	A	Y
4.2.2019	B	X
20.2.2019	B	Y
6.3.2019	A	X

The desired output is a table visual with an index number based TransactionDate (group rows by month) and Customer ID (group by unique ID)

Desired output, a table visual looks like this:

Index	Start of period	End of period	Customer	Number of purchases X	Number of purchases Y
1	1.2.2019	28.2.2019	A	2	1
2	1.2.2019	28.2.2019	B	1	1
3	1.3.2019	31.3.2019	A	1	0

As you can see, row 3 in the table visual is the same customer A but the month is March, so a new index number (3) is assigned.

The last two columns I can do with measures and the date attributes I can do with calculated columns using EOMONTH function.

Any hints on how this index number could be done in DAX? Thanks for any input!

[v-yulgu-msft · Accepted solution]

Hi @biz_wiz ,

Add two calculated columns in source table.

Start of period =DATE ( YEAR ( Sample1[TransactionDate] ), MONTH ( Sample1[TransactionDate] ), 1 )

End of period = EOMONTH(Sample1[Start of period],0)

New measures.

index based = SELECTEDVALUE(Sample1[Start of period])&SELECTEDVALUE(Sample1[Customer ID])

Index = RANKX(ALLSELECTED(Sample1),[index based],,ASC,Dense)

Number of purchases X = CALCULATE(COUNT(Sample1[Product]),Sample1[Product]="X")+0

Number of purchases Y = CALCULATE(COUNT(Sample1[Product]),Sample1[Product]="Y")+0

Best regards,

Yuliana Gu

[biz_wiz]

Thank you so much Yuliana 🙂 @v-yulgu-msft