Forum Discussion
Get the Status based on the date selected value
Hello,
I have a data that looks like this:
| Opportunity | Project | Project Name | Category Bk | Date |
| 042452503602QVayAAG | PROJ-19191 | Zoora Bank- Support on Telco | RC_CONSULTING_TIME_AND_MATERIALS | 11/07/2023 |
| RC_CONSULTING_TIME_AND_MATERIALS | 06/07/2023 | |||
| RC_CONSULTING_TIME_AND_MATERIALS | 05/07/2023 | |||
| RC_CONSULTING_TIME_AND_MATERIALS | 27/06/2023 | |||
| RC_CONSULTING_TIME_AND_MATERIALS | 22/06/2023 | |||
| RC_PS_TM | 05/01/2023 | |||
| RC_PS_TM | 04/01/2023 | |||
| RC_PS_TM | 03/01/2023 | |||
| RC_PS_TM | 22/12/2022 | |||
| RC_PS_TM | 21/12/2022 | |||
| RC_PS_TM | 20/12/2022 | |||
| RC_PS_TM | 19/12/2022 |
My objective is to show the 'Current Category BK' based on the date selected (like parameters in Tableau in showing the values off a date parameter)
I wanted to create a table, that will generate outut like this below:
| Opportunity | Project | Project Name | Current Category BK |
| 042452503602QVayAAG | PROJ-19191 | Zoora Bank- Support on Telco | RC_PS_TM |
Let's just assume that the Selected Date Parameter Dropdown:
| 05/01/2023 |
That is why the value for Current Category BK is 'RC_PS_TM'
In Power BI, what would be the formula for the Current Category BK?
I have already created a Date Parameter:
Hi cruzp ,
(1) As Ashish_Mathur said, click "transform data" to enter the power query editor, in the tab [transform] to find "replace values" in the empty replace null, and then click "fill down", the first three columns to fill, and then click "close and apply ".
(2)Create tables.
Calendar = CALENDARAUTO(12)New Table = 'Table'(3)Create measures.
SelectedDateValue = SELECTEDVALUE('Calendar'[Date])Flag = IF(MAX('New Table'[Date])=[SelectedDateValue],1,0)Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi,
In the Query Editor, use the "Fill Down" feature to fill in the blanks of the first 3 columns. Create a Calendar Table and a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. Create a slicer from the Date column of the Calendar Table and select a Date. To a Table visual jst drag the 4 columns.
Hope this helps.
Hi cruzp ,
(1) As Ashish_Mathur said, click "transform data" to enter the power query editor, in the tab [transform] to find "replace values" in the empty replace null, and then click "fill down", the first three columns to fill, and then click "close and apply ".
(2)Create tables.
Calendar = CALENDARAUTO(12)New Table = 'Table'(3)Create measures.
SelectedDateValue = SELECTEDVALUE('Calendar'[Date])Flag = IF(MAX('New Table'[Date])=[SelectedDateValue],1,0)Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous thank you for the help!!
just a follow up,
Does this logic work if I create another field for Previous Category BK then put it next to the Current Category BK?
Since we already have one for the Current, i need to have another calendar slicer for the Previous. How do i do that?
SO IMAGINE 22/06/2023 is selected for the Previous Calendar Date,
this will be the expected output:Opportunity Project Project Name Current Category BK Previous Category BK 042452503602QVayAAG PROJ-19191 Zoora Bank- Support on Telco RC_PS_TM RC_CONSULTING_TIME_AND_MATERIALS Hi cruzp ,
(1)Creates a second calendar table as a slicer to select the previous date.
Calendar2 = CALENDARAUTO(12)(2)Create a measure.
Previous Category BK = CALCULATE(MAX('Table'[Category Bk]),FILTER(ALL('Table'),'Table'[Date]=SELECTEDVALUE('Calendar2'[Date])))Best Regards,
Neeko Tang
