Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | 107 | 0.02% | 539,166 | 1,329 | 0.25% |
Total CA Accounts | 324,921 | 324,760 | 161 | 0.05% | 323,644 | 1,277 | 0.39% |
Total TX Accounts | 165,534 | 165,091 | 443 | 0.27% | 160,686 | 4,848 | 3.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.
AccountNumber | State | Period |
3453564 | NY | Sep-19 |
3650122 | TX | Sep-19 |
8375968 | CA | Sep-19 |
7256553 | CA | Sep-19 |
2488654 | NY | Sep-19 |
3453564 | NY | Nov-19 |
3650122 | TX | Nov-19 |
8375968 | CA | Nov-19 |
7256553 | CA | Nov-19 |
2488654 | NY | Nov-19 |
3453564 | NY | Dec-19 |
3650122 | TX | Dec-19 |
7256553 | CA | Dec-19 |
2488654 | NY | Dec-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!
Solved! Go to Solution.
Hi,
Hope this is what you want:
See my attached pbix file.
Best Regards,
Giotto
@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
AccountNumber | State | Gender | CustomerID | Period |
3453564 | NY | F | 1001 | 19-Sep |
3650122 | TX | M | 1002 | 19-Sep |
8375968 | CA | M | 1003 | 19-Sep |
7256553 | CA | M | 1003 | 19-Sep |
2488654 | NY | F | 1001 | 19-Sep |
3453564 | NY | F | 1001 | 19-Nov |
3650122 | TX | M | 1002 | 19-Nov |
8375968 | CA | M | 1003 | 19-Nov |
7256553 | CA | M | 1003 | 19-Nov |
2488654 | NY | F | 1001 | 19-Nov |
3453564 | NY | F | 1001 | 19-Dec |
3650122 | TX | M | 1002 | 19-Dec |
7256553 | CA | M | 1003 | 19-Dec |
2488654 | NY | F | 1001 | 19-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!
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.
Thanks!
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |