Delta of a Running Total on different dates - how to approach

[DriesD]

In case you've seen my other first post here on the forum, this is my second. As part of an exercise of bringing existing Tableau reporting into PowerBi I run into the following problem:

I have two quick measures - Running Total, one on the 'Created on Date' (which always has data) and second on the 'Technical Completion Date' (which does not always have data).

I want to bring these two together in the same graph, and for visualization purposes, also map the delta between these two. 
The purpose here is to see how quick they are ramping up creation and if the technical completion is keeping up. 

The issue is that both these running totals are on different dates so I cannot seem to bring them in the same graph. 
In earlier reporting we would add a column in the data file, stating if it's 'created' or 'completed', but this does not seem like a good approach in powerbi.

Appreciate those that can teach me something here!

Sample data:

Ordernumber	Created On	Technical Completion
1001	1/5/2020
1002	2/5/2020	1/5/2020
1003	3/5/2020	3/5/2020
1004	4/5/2020	5/5/2020
1005	5/5/2020	5/5/2020
1006	6/5/2020

[v-shex-msft]

Hi @DriesD,

Perhaps you can try to create a calculated table to expand all records between your date fields, then link to raw table based on 'order' field and you can simply use new table date to analyze raw table records across their date fields:

Expand =
VAR _calendar =
    CALENDAR (
        MIN ( Sample[Created On] ),
        MAX ( MAX ( Sample[Technical Completion] ), TODAY () )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Sample, _calendar ),
            Sample[Created On] <= [Date]
                && IF (
                    Sample[Technical Completion] <> BLANK (),
                    Sample[Technical Completion] >= [Date],
                    TODAY () >= [Date]
                )
        ),
        "Ordernumber", [Ordernumber],
        "Date", [Date]
    )

Reference:
Spread revenue across period based on start and end date, slice and dase this using different dates 
Regards,
Xiaoxin Sheng

[sanimesa · Accepted solution]

@DriesD  You will need a calendar table.

1. Then create a relationship between the date in calendar table to the created on

2. and between calendar table date to completion date

 - only one relationship can be active, the other will show as dotted line.

Create the first measure as you would normally. For the second measure, you will need to use the DAX expression USERELATIONSHIP.

On your visual, you drop the date from Calendar table on the x-axis. Add the two measures as values.

I think this approach could work out for you.

[DriesD]

@sanimesa , would you mind elaborating on the userelationship?

So I've added in that date table, and dropped that date to the two dates in my original table

'Created On' and 'Technical Completion'

I did the first measure like the original one, but referred to the Date in my calendar table, or should I refer to the 'Created On' from my original table?

Next, I'm not sure how to put USERELATIONSHIP in there..

[sanimesa]

@DriesDIf you want to do the same measure but for the Technical Completion date for instance, you will need to use the UseRelationship. If you do not use UseRelationship and create a measure which uses Date from the Calendar table, it will by default filter it on the Created date since that's the one with an Active relationship (see the two lines, one is dotted, only one relationship can be active at any one point).

[DriesD]

this is basically my end result: 

- 2 measures in my date table, COUNTA() against the date field I want to count in the original table, followed by two additional measures to get the cumulative count:

Cumulative Orders TECO = CALCULATE([CountOrders Techn.Compl.],FILTER(all(CalendarTable[Date]),CalendarTable[Date]<=MAX(CalendarTable[Date])),USERELATIONSHIP(IW73[Technical completion],CalendarTable[Date]))

did this as well for the other date value, without the userelationship and last measures to calculate the delta between the two