Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
cruzp
Helper III
Helper III

Get the Status based on the date selected value

Hello,

I have a data that looks like this:

OpportunityProjectProject NameCategory BkDate
042452503602QVayAAGPROJ-19191Zoora Bank- Support on TelcoRC_CONSULTING_TIME_AND_MATERIALS11/07/2023
   RC_CONSULTING_TIME_AND_MATERIALS06/07/2023
   RC_CONSULTING_TIME_AND_MATERIALS05/07/2023
   RC_CONSULTING_TIME_AND_MATERIALS27/06/2023
   RC_CONSULTING_TIME_AND_MATERIALS22/06/2023
   RC_PS_TM05/01/2023
   RC_PS_TM04/01/2023
   RC_PS_TM03/01/2023
   RC_PS_TM22/12/2022
   RC_PS_TM21/12/2022
   RC_PS_TM20/12/2022
   RC_PS_TM19/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:

 

OpportunityProjectProject NameCurrent Category BK
042452503602QVayAAGPROJ-19191Zoora Bank- Support on TelcoRC_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())
1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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 ".

vtangjiemsft_0-1689660066584.png

vtangjiemsft_2-1689660285491.png

(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)

 

vtangjiemsft_3-1689662008804.png

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. 

View solution in original post

6 REPLIES 6
v-tangjie-msft
Community Support
Community Support

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 ".

vtangjiemsft_0-1689660066584.png

vtangjiemsft_2-1689660285491.png

(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)

 

vtangjiemsft_3-1689662008804.png

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. 

@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:

OpportunityProjectProject NameCurrent Category BKPrevious Category BK
042452503602QVayAAGPROJ-19191Zoora Bank- Support on TelcoRC_PS_TMRC_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])))

vtangjiemsft_0-1689903324575.png

Best Regards,

Neeko Tang

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

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.