Show trend analysis on a line chart

[sarath5140]

Hi, please help me in solving my below problem.

I have two tables one is incident table and other table is incident postmortem table.
Both the tables are having Id and date time column and these tables are not related. Now, i want to represent them in a line to see the trend for last 6 months till today in a line chart and axis will be my month and values will be the count of ticket ids for both the tables. How can i acheive this, please throw some insights.

[v-shex-msft]

Hi @sarath5140 ,

You can create a calendar table as axis of line chart, then write a measure to lookup and summary records from two tables based on current calendar date and use in value fields. After these, you can enter to analytics panel and turn on trend line features.

If you are confused on coding formula, please provide some sample data with expect result to help us clear your requirement and do test and coding formula on it.

Regards,
Xiaoxin Sheng

[sarath5140]

Hi v-shex-msft, 

Thanks for your reply. Please find my below tables data.

Table A : Incident Table

IncidentId	CreationDate	Status
10605331	2/28/2019 4:16:58 PM	INPROGRESS
10500578	2/28/2019 3:52:21 PM	INPROGRESS
10500558	1/10/2019 3:52:21 PM	INPROGRESS
10697056	09/21/2018 3:46:16 PM	INPROGRESS
10635516	2/28/2019 3:30:45 PM	INPROGRESS

Table B : Postmortem Data

IncidentId	SourceCreateDate	Status
8193170	2019-02-28 16:48:01.0000000	RESOLVED
4058843	2018-02-28 16:48:00.0000000	ACTIVE
405884398	2019-01-27 16:48:00.0000000	RESOLVED
81930031	2018-02-21 16:48:00.0000000	ACTIVE

Please help me with the Dax formulas and measures and to acheive the trend for both the tables to show the incidentid count vs month trend in line chart.

Thanks

[v-shex-msft]

HI @sarath5140 ,

You can take a look at following sample.

Measure formulas:

InProgress =
VAR currDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Incident[IncidentId] ) ),
        FILTER (
            ALLSELECTED ( Incident ),
            [CreationDate]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 6, DAY ( currDate ) )
                && [CreationDate] <= currDate
                && [Status] = "INPROGRESS"
        )
    )


Resolve = 
VAR currDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Postmortem[IncidentId] ) ),
        FILTER (
            ALLSELECTED ( Postmortem ),
            [SourceCreateDate]
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 6, DAY ( currDate ) )
                && [SourceCreateDate] <= currDate
                && [Status] = "RESOLVED"
        )
    )

Create a line chart with calendar date and two measures, then add a trend line and turn off combine series option.

Regards,

Xiaoxin Sheng