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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-gizhi-msft
Community Support
Community Support

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
v-gizhi-msft
Community Support
Community Support

Hi,

 

Hope this is what you want:

6.PNG

See my attached pbix file.

 

Best Regards,

Giotto

Anonymous
Not applicable

@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!

Anonymous
Not applicable

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!

amitchandak
Super User
Super User

@Anonymous , 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

camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

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!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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