Forum Discussion
Microsoft EmployeeDynamic calculation based on slicer selection to compare two months values
Hi,
I have been trying to work on this for quite some time but not able to find the solution:
I have a table like this in which there could be a change in state of the program every month. what i need is an additional column that could show the value based on the slicer selection.
| Month Year | Program | State |
| 1/1/2023 | ABC01 | Charter |
| 1/1/2023 | ABC02 | Charter |
| 1/1/2023 | ABC03 | Closure |
| 1/1/2023 | ABC04 | Cancelled |
| 2/1/2023 | ABC01 | Charter |
| 2/1/2023 | ABC02 | Charter |
| 2/1/2023 | ABC03 | Closure |
| 2/1/2023 | ABC04 | Cancelled |
| 2/1/2023 | ABC05 | Approved |
| 3/1/2023 | ABC01 | Charter |
| 3/1/2023 | ABC02 | Charter |
| 3/1/2023 | ABC03 | Closure |
| 3/1/2023 | ABC04 | Cancelled |
| 3/1/2023 | ABC05 | Approved |
| 3/1/2023 | ABC06 | Charter |
| 3/1/2023 | ABC07 | Approved |
For example slicer selection is Jan 2023 and March 2023. My output should look like this :
| Month Year | Program | State | PrevMonthState |
| 1/1/2023 | ABC01 | Charter | |
| 1/1/2023 | ABC02 | Charter | |
| 1/1/2023 | ABC03 | Closure | |
| 1/1/2023 | ABC04 | Cancelled | |
| 3/1/2023 | ABC01 | Charter | Charter |
| 3/1/2023 | ABC02 | Charter | Charter |
| 3/1/2023 | ABC03 | Closure | Closure |
| 3/1/2023 | ABC04 | Cancelled | Cancelled |
| 3/1/2023 | ABC05 | Approved | |
| 3/1/2023 | ABC06 | Charter | |
| 3/1/2023 | ABC07 | Approved |
5 Replies
- ThxAlot
Super User
.
powerbi0718its not always going to be previous month. depending upon what user is selecting it could be Jan 2023 and April 2023 or Jan 2023 and Feb 2023 or Feb 2023 and Aug 2023
- amitchandak
powerbi0718 , you can use a date table joined with the date of your table and time intelligence
calculate(max(Table[Status]), previousmonth(Date[date]))
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- ThxAlot
amitchandak I dont' need your comment.
Obviously, your formula is error-prone; if any other columns exists in the viz, it's damaged; ALLEXCEPT() is necessary.
Anyway, talk to the thread initiator.
- amitchandak
ThxAlot , Sorry about mentioning your name, when I initially typed the name I got confused because it showed two names and one blank update below. So I thought you were the tread owner.
I corrected the name just a couple of minutes back.
