Need to return latest value in a column chart

[Anonymous]

Hello everyone,

I have a table sample as follows

id	Date	Old Value	New Value
1	01-01-2020		Step 1
1	01-02-2020	Step 1	Step 2
1	01-03-2020	Step 2	Step 3
2	01-04-2020		step 1
2	01-05-2020	Step 1	Step 2

I want to create a column chart where :

• axis = [Date]
• and column values = count of each [New value] value
• and it should keep the latest value for each id; for example in '01-05-2020' i should have 1 "Step 2" and 1 "Step 3"

I was told that the EARLIER function could help but I don't know how it works.

Thanks in advance 🙂

[Ashish_Mathur]

Hi,

Could you clearly show the expected result in a Table format.

[Anonymous]

id	new value	Date
1	Step 3	>=01-03-2020
1	Step 2	>= 01-02-2020 and <01-03-2020
1	Step 1	>=01-01-2020 and < 01-02-2020

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please review the content in the following links and check whether the custom visual Gantt is what you want. 

GANTT chart visuals in Power BI

Power BI Custom Visuals - Gantt

Best Regards

[Anonymous]

Hi @Anonymous 

Take a look at my example below:

The logic is grouping the IDs and taking the max string from the field 'Old Value'. The process is duplicated for 'New Value'. I then went ahead and applied a value for each 'Step'.

This is the graph when using the new values.

Hope this helps and please leave a thumbs if it works!

-JID

[Anonymous]

The 3 steps was just to simplify the process but in reality I have 7. I don't want to create a new column for each one ..

[Anonymous]

@Anonymous Im not sure what you mean when you say "The 3 steps was just to simplify the process but in reality I have 7." Below i have an example with "Step 1" - "Step 7". You do not need to add new columns, just update the SWITCH DAX to include all steps.

[Anonymous]

Another problem ; what will the graph show in Jan 16th ?

[Anonymous]

The graph will display the highest "Old" and "New" value per ID.

[StefanoGrimaldi]

some documentation that could help for this: https://docs.microsoft.com/en-us/dax/earlier-function-dax

hope it helps you working around the formula you looking for