Get the Status based on the date selected value

[cruzp]

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:

SelectedDateValue = SELECTEDVALUE(REP_REVENUE[DATE_BK],NOW())

[v-tangjie-msft · Accepted solution]

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. 

[cruzp]

@v-tangjie-msft 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

[v-tangjie-msft]

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

[cruzp]

Hi @v-tangjie-msft , how do i modify the DAX if the value is BLANK or the selected date does not have a category value, it will show "Null"?

The formula you provided is fine but it is showing empty/blank. I wanted to show "Null" if its empty/blank

[v-tangjie-msft]

Hi @cruzp ,

Try it.

Previous Category BK =
var _a= CALCULATE(MAX('Table'[Category Bk]),FILTER(ALL('Table'),'Table'[Date]=SELECTEDVALUE('Calendar2'[Date])))
return if(_a=BLANK(),"Null",_a)

Best Regards,

Neeko Tang

[Ashish_Mathur]

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.