Forum Discussion
Working with multiple date columns
Hmm not succeeding yet unfortunately. The article uses one single date column, and it is useful for comparing the same measure from different periods. But I have one table with 2 different date columns and want to plot them simultaneously.
So let's say we have 2 orderlines from an orders table. Including Intake Date and value and Shipment date and value (which can be lower on incomplete delivery) of the order:
Order No | Intake Date | Intake Value | Shipment Date | Shipment Value
1 | March 3 2019 | 5.000 | April 12 2019 | 4.000
2 | April 20 2019 | 10.000 | May 2 2019 | 10.000
I want to plot both intake and shipment on a single date axis in a single graph.
So in a graph showing March, April, May 2019;
March needs to show an intake of 5.000 and a shipment of 0
April needs to show an intake of 10.000 and a shipment of 4.000
May needs to show an intake of 0 and a shipment of 10.000
Try this, you can create more than on relation in power BI. Few of them can be inactive. Means, join both dates with the same time dim and keep one active.
This sample formula to use one relation at a time
Sales 7 =
VAR Column1=
SELECTEDVALUE ( dim[DimColumn1])
VAR Column2 =
SELECTEDVALUE ( Sales[Second])
var Column3 =SUMMARIZE(Sales,Sales[Column1])
var Column4 =SUMMARIZE(Sales,Sales[Column2])
RETURN
CALCULATE( SUM (Sales[Sales]),
dim[DimColumn1] in Column3 ,USERELATIONSHIP(dim[DimColumn1],Sales[First])
)+CALCULATE( SUM (Sales[Sales]),
dim[DimColumn1] in Column4 ,USERELATIONSHIP(dim[DimColumn1],Sales[Second])
)
Sorry, but could you show example data with it? I'm a bit confused what column1, column2 etc are meant to be.