Forum Discussion

PhiBu's avatar
PhiBu
Icon for Helper I rankHelper I
6 years ago
Solved

Line Chart with two diffrent dates on x-axes

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_createdDate_resolved
A12.19.201901.28.2020
B02.01.202003.01.2020
C05.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. 
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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