Compare Sales to Last Month by Exact Same Sales Days

[EddyW]

Hello Everyone and @ImkeF,

I want to ask about Sales Data. Recently, i watched the youtube video about using DAX formula related to Time Intelligence (such as DATESMTD, DATEADD, PARALLELPERIOD, etc). However, no such DAX that fulfill my needs, since i need to do indexing the sales date manually.

The reason i need this is to compare the sales date month-to-month within the exact same working days. So that the comparison will be precise and apple-to-apple each month.

As Is

SalesDate	PrincipalAmt	DatesMTD	Indexing in Day()
01-Oct-22	18,726,838	18,726,838	1
03-Oct-22	32,035,711	50,762,549	3
04-Oct-22	33,111,669	83,874,219	4
01-Nov-22	24,011,148	24,011,148	1
02-Nov-22	25,004,098	49,015,246	2
03-Nov-22	21,573,716	70,588,962	3

As Is Summary

As Is	Calendar Days	01-03 November (3 Calendar Days)	01-03 November (3 Calendar Days)
	3	70,588,962	50,762,549

To Be:

SalesDate	PrincipalAmt	DatesMTD	Indexing in Excel
01-Oct-22	18,726,838	18,726,838	1
03-Oct-22	32,035,711	50,762,549	2
04-Oct-22	33,111,669	83,874,219	3
01-Nov-22	24,011,148	24,011,148	1
02-Nov-22	25,004,098	49,015,246	2
03-Nov-22	21,573,716	70,588,962	3

To Be Summary

To Be	Sales Days	01-03 November (3 Sales Days)	01-04 October (3 Sales Days)
	3	70,588,962	83,874,219

I can do this in excel (attached) by using Pivot and then i do a little bit manual by typing each row or applying Excel Formula, but i don't know how to apply it in powerBI. I believe i need the M-Code rather than DAX because i will run the script directly (using Direct Query Mode) into the Database and generate index like in excel.

SalesData.xlsx

Thankyou in Advance.

Best Regards,

Eddy W.

[v-jingzhang · Accepted solution]

Hi @EddyW

You can add a calculated column with below DAX to have an Index column.

Index = COUNTROWS(FILTER('Table','Table'[YearMonth]=EARLIER('Table'[YearMonth])&&'Table'[SalesDate]<=EARLIER('Table'[SalesDate])))

Then get the expected result in a matrix visual. You can use the "DatesMTD" column from the current table, or create a MTD measure with "PrincipalAmt" and "Index". 

I have attached the sample file at bottom. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[EddyW]

Hi @v-jingzhang,

Ok then, i will eitheir create the DB under the import mode or discuss with my IT team.

Thank you so much for the solution!

Best Regards,

Eddy W.

[v-jingzhang · Accepted solution]

Hi @EddyW

You can add a calculated column with below DAX to have an Index column.

Index = COUNTROWS(FILTER('Table','Table'[YearMonth]=EARLIER('Table'[YearMonth])&&'Table'[SalesDate]<=EARLIER('Table'[SalesDate])))

Then get the expected result in a matrix visual. You can use the "DatesMTD" column from the current table, or create a MTD measure with "PrincipalAmt" and "Index". 

I have attached the sample file at bottom. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[EddyW]

Hi @v-jingzhang,

Thankyou for the helps, i believe it works. However, when i tried to apply it, it didn't work because i was using DirectQuery mode. Is there another way to do the indexing under DirectQuery mode instead of Import mode?

Best Regards,

Eddy W.

[v-jingzhang · Accepted solution]

Hi @EddyW 

Sorry I cannot think of a solution under DirectQuery mode. Power Query can create this indexing in every month group but it isn't supported under DirectQuery either. To use DirectQuery, it seems the indexing column needs to be added in the data source. 

Best Regards,
Jing