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.
Hello,
I am new to Power BI. I have a table with below columne
I want to create a matrix in my dashboard to show the open orders for each customer for previous month
so in Feb, I want to only show open orders from Jan for that customer
Customer | Month | Open Orders | Fulfilled Orders |
A | Jan-20 | 0 | 3 |
A | Feb-20 | 3 | 5 |
A | Mar-20 | 2 | 10 |
A | Apr-20 | 5 | 10 |
B | Jan-20 | 0 | 5 |
B | Feb-20 | 4 | 6 |
B | Mar-20 | 8 | 10 |
B | Apr-20 | 20 | 80 |
So for March I want to show below matrix
Customer | Feb-20 |
A | 3 |
B | 4 |
Is it possible?
Solved! Go to Solution.
hi @Anonymous
You could use time intelligence function PREVIOUSMONTH or DATEADD to get it easily.
https://docs.microsoft.com/en-us/dax/previousmonth-function-dax
https://docs.microsoft.com/en-us/dax/dateadd-function-dax
and 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
PREVIOUS MONTH Data = CALCULATE(SUM('Table'[Open Orders]),PREVIOUSMONTH('Date'[Date]))
PREVIOUS MONTH Data 2 = CALCULATE(SUM('Table'[Open Orders]),DATEADD('Date'[Date],-1,MONTH))
Result:
and here is sample pbix file, please try it.
Regards,
Lin
prefer using datesmtd and totalmtd with date calendar. Make sure you have date calendar
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),
Date[Date]<date(year(today()),month(today())-1,day(today())))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
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/
@Anonymous ,
You can try below DAX measure to get your expected result:
hi @Anonymous
You could use time intelligence function PREVIOUSMONTH or DATEADD to get it easily.
https://docs.microsoft.com/en-us/dax/previousmonth-function-dax
https://docs.microsoft.com/en-us/dax/dateadd-function-dax
and 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
PREVIOUS MONTH Data = CALCULATE(SUM('Table'[Open Orders]),PREVIOUSMONTH('Date'[Date]))
PREVIOUS MONTH Data 2 = CALCULATE(SUM('Table'[Open Orders]),DATEADD('Date'[Date],-1,MONTH))
Result:
and here is sample pbix file, please try it.
Regards,
Lin
So, generally you would use PREVIOUSMONTH for that. But, if the time intelligence functions vex you, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
I will check it out, thanks Greg
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 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |