cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Compare Current Month Data to Prior Month/Quarter in the same table

Hi everyone,

 

I'm trying to make a table/matrix that compares account counts in the current month to the prior month, and to the prior quarter end. And then calculating the count difference, and the % difference. Also I'd need a slicer that can change what the "Current" month is, so if I select Dec 2019, the previous month would be Nov 2019, and Previous Quarter is Sept 2019.

 

  Current Month  Previous Month  Month # Change Month % Change Previous Quarter  Quarter # Change Quarter % Change
Total NY Accounts          540,495            540,388                     1070.02%              539,166                  1,3290.25%
Total CA Accounts          324,921            324,760                     1610.05%              323,644                  1,2770.39%
Total TX Accounts          165,534            165,091                     4430.27%              160,686                  4,8483.02%

 

My data table is pretty basic, would look something like this, where every month I get all the accounts we have in the system.

AccountNumberStatePeriod
3453564NYSep-19
3650122TXSep-19
8375968CASep-19
7256553CASep-19
2488654NYSep-19
3453564NYNov-19
3650122TXNov-19
8375968CANov-19
7256553CANov-19
2488654NYNov-19
3453564NYDec-19
3650122TXDec-19
7256553CADec-19
2488654NYDec-19

 

Getting the current month is easy and done, but not sure how I would get the prior month/quarter and then the #/% differences to be populated per row, and then change when I select a new Current Month?

 

Thanks so much!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Compare Current Month Data to Prior Month/Quarter in the same table

Hi,

 

Hope this is what you want:

6.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Compare Current Month Data to Prior Month/Quarter in the same table

Hi @Kofo ,

 

You can use intel date functions if you have a date table:

 

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

 

It has PREVIOUSQUARTER and PREVIOUSMONTH.

 

Ricardo


Did I answer your question? Mark my post as a solution!
Ricardo

Highlighted
Super User IV
Super User IV

Re: Compare Current Month Data to Prior Month/Quarter in the same table

@Kofo , Hope you have date.  You can use the following type of formula

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(Table[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd(Table[Date],-12,MONTH),"8/31")))





QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD((Table[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],-1,QUARTER)))
CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd(Table[Date],-1,QUARTER))))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd(Table[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd(Table[Date],-1,Year))))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-4,QUARTER))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

https://www.youtube.com/watch?v=FIkkhcGDgiM



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Compare Current Month Data to Prior Month/Quarter in the same table

Hi,

 

Hope this is what you want:

6.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

Highlighted
Helper I
Helper I

Re: Compare Current Month Data to Prior Month/Quarter in the same table

@v-gizhi-msft This is amazing! Thank you so much.

 

Say there's more than just Account Number and State I'd like to focus on in the same table, say Gender and CustomerID

AccountNumberStateGenderCustomerIDPeriod
3453564NYF100119-Sep
3650122TXM100219-Sep
8375968CAM100319-Sep
7256553CAM100319-Sep
2488654NYF100119-Sep
3453564NYF100119-Nov
3650122TXM100219-Nov
8375968CAM100319-Nov
7256553CAM100319-Nov
2488654NYF100119-Nov
3453564NYF100119-Dec
3650122TXM100219-Dec
7256553CAM100319-Dec
2488654NYF100119-Dec

 

And on my report table I'd like additional rows for Total Male Customers and Total Female customers. Trying it with your current work and your Target table with the category/state is throwing me for a loop.

 

Thanks!

Helper I
Helper I

Re: Compare Current Month Data to Prior Month/Quarter in the same table

Not sure I'm allowed to bump this thread, or if I should make a new one? Still having a tough time when I want to calculate counts based on different columns.

 

@v-gizhi-msft ?

 

Thanks!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors