cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Compare Sales to Last Month by Exact Same Sales Days

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

Best Regards,

Eddy W.

2 ACCEPTED SOLUTIONS
Community Support

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.

Community Support

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

4 REPLIES 4
Frequent Visitor

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.

Community Support

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.

Frequent Visitor

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.

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors