Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |