Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Show different date dimension on two axis

Hi all,   I've tried finding a similar topic but to no avail so far - hopefully you can help! I have a table of Orders and a table with Contracts. The dates in the tables are in an Oracle format, ...
Need Help
  • vickyArce's avatar
    vickyArce
    7 years ago

    What you can do is:

    1. In order table, merge to Conversion_Date table to get the new PromisedShipDate.Date, which is a DateTime format column;Merge to Conversion_Date table again to get the new ActualShipDate.Date

    2. add columns in the Order table with Name : [Feb-19], [Mrt-19],[Apr-19]  etc, the Power Query M language script will be like = if Date.Month([ActualShipDate.Date]) = 2 then [Quantity] else 0.

    3. create a table in report view with columns you need.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

Anonymous you can use something like this, basically userelationship will use inactive relations as active.

 

Qty = 
CALCULATE( SUM( 'Order'[Quantity] ), 
    USERELATIONSHIP( Contract[Contract number], 'Order'[Contract] ),
    USERELATIONSHIP( Contract[Contract month], 'Converted Date'[Date to convert] )
    )

Also not sure if keeping date_dimension in model has any value, since you already have converted date dimension, you should keep that and remove the other one.

vickyArce's avatar
vickyArce
Frequent Visitor
7 years ago

What you can do is:

1. In order table, merge to Conversion_Date table to get the new PromisedShipDate.Date, which is a DateTime format column;Merge to Conversion_Date table again to get the new ActualShipDate.Date

2. add columns in the Order table with Name : [Feb-19], [Mrt-19],[Apr-19]  etc, the Power Query M language script will be like = if Date.Month([ActualShipDate.Date]) = 2 then [Quantity] else 0.

3. create a table in report view with columns you need.