Divide function on two different date field | Calculate quota

[lennardheyder]

Dear community, following situation:

We would like to calculate the ration between cancelled orders and incoming orders. Challenge: There is one date field for incoming orders (Creation date) and one date field for cancellations (changed on date). I’m struggling now with a) the visualization over time (which date to take) and b) the calculation itself. I started with a calculation purely based on the changed on date which is obviously not sufficient....

At the end I would like to show Cancelled orders (changed on date) / incoming orders (creation date) and show the visulisation on the creation date time line graph.

Please find attached some screenshots:

[v-jingzhang · Accepted solution]

Hi @lennardheyder 

This article (Using USERELATIONSHIP in DAX - SQLBI) is similar to your scenario. It also has two date fields in a table. Please read it to get some basic ideas first. Then you can try the following steps:

1. Add a Date or Calendar table to your model. Make sure it covers all dates that may appear in Creation Date and Changed On Date columns. How to Create Date Tables in Power BI Tutorial | DataCamp

2. Build relationships from Date table's Date column to Creation Date and Changed On Date in Fact table. As only one relationship can be active between two tables, one relationship is active and the other one is inactive. 

3. Use Date table's Date column on X-axis of line charts. 

4. Assume the active relationship is between Date[Date] and 'Fact Table'[Creation Date], you can create the following measures. USERELATIONSHIP function is used to activate an inactive relationship in calculation. I assume that you use Count for aggregation. 

Number of Incoming = COUNT ( 'Fact Table'[Item] )

Number of Cancelled =
CALCULATE (
    COUNT ( 'Fact Table'[Item] ),
    USERELATIONSHIP ( 'Date'[Date], 'Fact Table'[Changed On Date] )
)

5. Add above measures to Y-axis of the line chart. 

6.  Create the third measure for the ratio. Add it to the line chart. 

Ratio = DIVIDE ( [Number of Cancelled], [Number of Incoming] )

Hope this helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lennardheyder]

Great, that is the solution! Thank you