Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DriesD
Regular Visitor

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

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:

OrdernumberCreated OnTechnical Completion
10011/5/2020 
10022/5/20201/5/2020
10033/5/20203/5/2020
10044/5/20205/5/2020
10055/5/20205/5/2020
10066/5/2020 

 

1 ACCEPTED SOLUTION
sanimesa
Post Prodigy
Post Prodigy

@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.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
sanimesa
Post Prodigy
Post Prodigy

@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.

@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'

6-8-2020 0006.jpg

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?6-8-2020 0005.jpg

 

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

@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).

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.