Line Chart with two diffrent dates on x-axes

[PhiBu]

Hello everyone,
I am using my Jira-data to create an Dashboard in PowerBI. My goal is to create a line-chart which displays the cumulated resolved and created issues over time.

The table "Jira" for example looks like this: 

Key	Date_created	Date_resolved
A	12.19.2019	01.28.2020
B	02.01.2020	03.01.2020
C	05.06.2020

I use Measure to cumulated the created and resolved Issues over time. 

Cumulative_issues_created = CALCULATE(

     COUNT('Jira'[Date_created]),

     FILTER(ALLSELECTED('Jira'),

     'Jira'[Date_created] <= MAX('Jira'[Date_created]))

)

AND 

Cumulative_issues_resolved = CALCULATE(

     COUNT('Jira'[Date_resolved]),

     FILTER(ALLSELECTED('Jira'),

     'Jira'[Date_resolved] <= MAX('Jira'[Date_resolved]))

)

The Problem is, I dont know what data I should use for the x-axes of my line-chart. If I am using "Date_created", it for example looks like Key A is created and resolved in December. But the issue is created in December and resolved in January. 
Does someone is having a solution for this problem? 

Thanks in advance. 

[MFelix · Accepted solution]

Hi @PhiBu ,

To solve this issue you need to create a calendar table and use that as your x-axis value.

Now change your measures to:

Cumulative_issues_created = CALCULATE(
     COUNT('Jira'[Key ]);
     FILTER(ALL(Jira[Date_created]);
     'Jira'[Date_created] <= MAX('Calendar'[Date]))
)

Cumulative_issues_resolved = CALCULATE(
     COUNT('Jira'[Key ]);
     FILTER(ALLSELECTED(Jira[Date_resolved]);
     'Jira'[Date_resolved] <= MAX('Calendar'[Date]) && Jira[Date_resolved] <> BLANK())
 )

I'm assuming that the Created date does not have blank values.

Check PBIX file attach (file in April version).

[Anonymous · Accepted solution]

Hi @PhiBu,

You can also try to create a calculated table to expand date range and link to raw table records(key field), then you can use new table date fields with raw table value fields to calculate the count of records between date range:

Expand = 
VAR _calendar =
    CALENDAR (
        MIN ( Jira[Date_created] ),
        MAX ( MAX ( Jira[Date_resolved] ), TODAY () )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Jira, _calendar ),
            Jira[Date_created] <= [Date]
                && IF (
                    Jira[Date_resolved] <> BLANK (),
                    Jira[Date_resolved] >= [Date],
                    TODAY () >= [Date]
                )
        ),
        "Key", [Key],
        "Date", [Date]
    )

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng

[MFelix · Accepted solution]

Hi @PhiBu ,

To solve this issue you need to create a calendar table and use that as your x-axis value.

Now change your measures to:

Cumulative_issues_created = CALCULATE(
     COUNT('Jira'[Key ]);
     FILTER(ALL(Jira[Date_created]);
     'Jira'[Date_created] <= MAX('Calendar'[Date]))
)

Cumulative_issues_resolved = CALCULATE(
     COUNT('Jira'[Key ]);
     FILTER(ALLSELECTED(Jira[Date_resolved]);
     'Jira'[Date_resolved] <= MAX('Calendar'[Date]) && Jira[Date_resolved] <> BLANK())
 )

I'm assuming that the Created date does not have blank values.

Check PBIX file attach (file in April version).