Forum Discussion
Helper IIITable for Current and Previous month
Hello All,
Problem Statement: Comparison between the selected Current month and the selected Previous month.
1. You have to select the month from the slicer. (That will be the current month)
2. Now in a table visual you have to show the comparison between these two months(Like, you select 01 Nov so its previous month will be 01 Oct.
3. Now the main requirement is to show how many items have been removed or added and their details like cost, count, etc, which means listing of removed and added items.
4. Removed: Item which is present in the previous month but not in the current month. Added: Item which is present in the current month but not in the previous month.
Note: Month selection would be dynamic.
Refer to the below table for example:
| Product | Price | Date | ||
| Prod 1 | 10 | 05-Dec-22 | ||
| Prod 1 | 8 | 05-Nov-22 | ||
| Prod 2 | 12 | 05-Dec-22 | ||
| Prod 2 | 45 | 05-Nov-22 | ||
| Prod 3 | 15 | 05-Dec-22 | ||
| Prod 4 | 25 | 05-Nov-22 |
Please help resolve this issue.
1 Reply
- amitchandak
Super User
MintuBaruah , using date table and time intelligence you can get current month vs last month
example
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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))But if select 1 month and want to show 2 month (trend)
the slicer need to be on an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -2) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
