Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EddyW
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 IsCalendar Days01-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 BeSales Days01-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.

 

2 ACCEPTED SOLUTIONS
v-jingzhang
Community Support
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])))

vjingzhang_0-1670219650455.png

 

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". 

vjingzhang_1-1670219693235.png

 

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.

View solution in original post

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

View solution in original post

4 REPLIES 4
EddyW
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.

v-jingzhang
Community Support
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])))

vjingzhang_0-1670219650455.png

 

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". 

vjingzhang_1-1670219693235.png

 

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.

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.

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors