Mixed data formats in the same column (Date & Text)

[Sahibb32]

Hi All,

So i have been using this software for several months now and its been super beneficial so far.

My question is, I am combining historical oil prices & forward oil prices all on ONE graph. 

I have created it such that; in the column, I have the historical daily prices (Date) and then the forward prices (for the Month - as a Text ; i.e Month+1, Month+2 etc. - which is text).

Something like this. If i was looking at the Close of Business price on Friday (24/7) and i want the forward figures on that day too; the historicals are daily price, and the forwards are monthly prices.

22/7/20 = $...

23/7/20 = $...

24/7/20 = $...

M+1 = $...

M+2 = $...

M+3 = $...

...

I could just add an index to the 22/7 as the first point and follow on, but if i want to check the curves and historicals back in March for example, I want the index to pick up the first historical date and last forward month every time.

How can i order this so that the forward months always come after the dates in order?

Best regards,

Sahib 

[dax]

Hi @Sahibb32 , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sahibb32]

Hi,

THe aim is to create a dashboard, whereby I can look at the historicals & forward prices of oil prices all on ONE graph.

The historicals are in days (Date) and the forwards are in months (Text). The aim is to look at what position the curves were in at any time; i.e. if i wanted to see the historicals and forwards of oil prices back on the 9th March 2020 for example. 

I can create a model on excel whereby on a specific date (lets say the 24/7/20-last friday); it looks at historicals (before this date)  and forwards (after this date). But id rather BI perform this when i add a date slicer it picks up the historical and forward points at ANY date.

I have all data on my excel sheet; its just displaying it on power BI im not sure the best way to approach this.

Does this make more sense?

CHeers,

Sahib

[Greg_Deckler]

@Sahibb32 - You will need some kind of Sort By column I would think. An Index column in Power Query might do the trick if your imported data is sorted correctly.