Create a delta chart that use two other charts

[AlexEners]

Hi, i'm new with power BI and i will explain what i have and what i need to do.

I've done 2 different chart with date that came from the same query. The query result table is composed by 3 columns: "PDO" (alphanumeric), "StartDate" (date) and "EndDate (date)".

In the chart 1 i put StartDate as X (and format it to month and year) and put in Y the count of PDO. So with the "chart 1" i'll get the sum of all PDO that starts for each month. The "chart 2" is similar to "chart 1" with EndDate as X and count PDO as Y but it shows the PDO that ends for each month.

What i need to do is to create a chart that show for each month the difference between the PDO that start and the PDO that end on the same month.

How can i do this?

I hope i explained myself correctly with my English.

[v-jiascu-msft]

Hi @AlexEners,

Can you share the pbix file please? At least a sample.

Since you want to put two different date fields in one visual, you would need a new date table if you don't have one. Then establish relationships to "StartDate" (date) and "EndDate (date)". But only one relationship is active for this time. 

Finally you can use a measure like below. Please try out the demo in the attachment.

difference =
CALCULATE (
    COUNT ( Table1[Value] ),
    USERELATIONSHIP ( DateTable[Date], Table1[End Date] )
)
    - COUNT ( Table1[Value] )

Best Regards,

Dale

[AlexEners]

Thanks for reply.

Ok i will share the file: FIle Here

As you can see, i have 2 charts. The first show me started points for every months and, the second one, ended points for every months. For example i have in July 17 1442 started points in the first charts, and 578 ended point in the second.

My goal is to get a third chart that shows the difference by started point and ended point for every months. For example in July 17 of the new chart i should have 1442 - 578  = 864 points.

How can i do this?

[v-jiascu-msft · Accepted solution]

Hi @AlexEners,

Please check out the demo in the attachment.

1. Create a date table.

2. Create TWO relationship. Please check it out in the demo.

3. Create a measure.

Measure =
COUNT ( ActivedPoint[DataInzioFornituraMese] )
    - CALCULATE (
        COUNT ( ActivedPoint[DataSospensioneFornituraMese] ),
        USERELATIONSHIP ( ActivedPoint[DataSospensioneFornituraMese], 'Calendar'[Date] )
    )

Best Regards,

Dale

[AlexEners]

Hi thanks for your help. Maybe i get it, but i don't know why, in my real project, i can't create a table.

I'm using direct query. Is this the reason?

[v-jiascu-msft]

Hi @AlexEners,

No, you can't create a new table in the Direct Query mode. Maybe you can create it in the data source. Due to the two dates are in two different columns, you need a date table to bring them together.

Best Regards,

Dale

[AlexEners]

mmmm ok thanks a lot for your time. I'll try to do this in my database or trying other way.

[AlexEners]

Spoiler

---

@v-jiascu-msft wrote:

Hi @AlexEners,

Can you share the pbix file please? At least a sample.

Since you want to put two different date fields in one visual, you would need a new date table if you don't have one. Then establish relationships to "StartDate" (date) and "EndDate (date)". But only one relationship is active for this time. 

Finally you can use a measure like below. Please try out the demo in the attachment.

difference =
CALCULATE (
    COUNT ( Table1[Value] ),
    USERELATIONSHIP ( DateTable[Date], Table1[End Date] )
)
    - COUNT ( Table1[Value] )

Best Regards,

Dale

---

 Ok i'm sharing this example that explain what i've got for now. I have 2 different charts that show me for every month Started Point and Ended Point for example in July 17 i've 1442 started point and 578 ended point. What i need to do now is to get another chart that shows the difference between these data. So in the third chart i should have in July 17: 1442-578 = 864 points. How can i do this? Should i use your soluction? in that case can you explain that solution better?

https://drive.google.com/open?id=1sUeQpzV1iQ15K8wV0TYQOaf4cb41Efl2