Creating Trends Over time

[powellar]

Hi there,

I am in the process of building a report which can be used to show data for a snapshot in time (easy and done) and then trends over the course of other excel sheets. I have Plan of Action and Milestones data for a number of systems that I can export out ad hoc, but typcially do it weekly. I can import three of the weekly reports and have three week's worth of data in one report, but I can't figure out how to create a line chart for the trends of the data cumulatively. Example Closed POAMs week over week, Delayed POAMS. I feel like this can be easily done but I can't figure out how to do it. I can create three charts on one tab with each week's data, but combining them is the issue. So for example a trend graph showing completed POAMS over the three reports, or delayed poams over the weeks, etc.

Looking for something like this:

Sample data would be:
one file

POAM ID	POAM Title	Detailed Weakness Description	Create Date	Days Since Created	Status	User Identified Criticality
11	test poam title	test weakness	1/1/2024	253	Delayed	High
12	test poam title	test weakness	3/1/2024	193	Delayed	High
13	test poam title	test weakness	6/1/2024	101	In Progress	High
18	test poam title	test weakness	7/1/2024	71	In Progress	Low
19	test poam title	test weakness	8/1/2024	40	Completed	Medium
27	test poam title	test weakness	8/17/2024	24	Completed	High

another file

POAM ID	POAM Title	Detailed Weakness Description	Create Date	Days Since Created	Status
11	test poam title	test weakness	1/1/2024	239	Delayed
12	test poam title	test weakness	3/1/2024	179	In Progress
13	test poam title	test weakness	6/1/2024	87	In Progress
18	test poam title	test weakness	7/1/2024	57	In Progress
19	test poam title	test weakness	8/1/2024	26	In Progress
27	test poam title	test weakness	8/17/2024	10	Completed

Last file

POAM ID	POAM Title	Detailed Weakness Description	Create Date	Days Since Created	Status
11	test poam title	test weakness	1/1/2024	230	Delayed
12	test poam title	test weakness	3/1/2024	170	In Progress
13	test poam title	test weakness	6/1/2024	78	In Progress
18	test poam title	test weakness	7/1/2024	48	In Progress
19	test poam title	test weakness	8/1/2024	17	In Progress
27	test poam title	test weakness	8/17/2024	1	In Progress

Thanks in advance!

[Anonymous]

Hi @powellar ,
Here are some steps you can try
Sample data
Table 1

Date	Poam
8/21/2024	Poam1
8/21/2024	Poam2
8/28/2024	Poam3
8/28/2024	Poam4
8/28/2024	Poam5
9/4/2024	Poam6
9/4/2024	Poam7
9/4/2024	Poam8
9/4/2024	Poam9
9/11/2024	Poam10
9/11/2024	Poam11

Table2

Date	Poam
8/21/2024	Poam1
8/21/2024	Poam2
8/21/2024	Poam3
8/28/2024	Poam4
8/28/2024	Poam5
8/28/2024	Poam6
9/4/2024	Poam7
9/4/2024	Poam8
9/11/2024	Poam9
9/11/2024	Poam10
9/11/2024	Poam11

Create measures

Difference = 
VAR _count = 
CALCULATE(
    COUNT('Table 1'[Date]),
    ALLEXCEPT(
        'Table 1',
        'Table 1'[Date]
    )
)
VAR PreviousCount = 
CALCULATE(
    COUNT('Table 1'[Date]),
    ALLEXCEPT(
        'Table 1',
        'Table 1'[Date]
    ),
    DATEADD('Table 1'[Date], -7, DAY)
)
RETURN
IF(
    ISBLANK(PreviousCount),
    _count,
    _count - PreviousCount
)

NextDifference = 
VAR NextDifference = 
CALCULATE(
    [Difference],
    ALLEXCEPT(
        'Table 1',
        'Table 1'[Date]
    ),
    DATEADD('Table 1'[Date], +7, DAY)
)
RETURN
IF(
    NextDifference = BLANK(),
    0,
    NextDifference
)

IsBreakingPoint = 
IF(
    [NextDifference] <= 0 && [Difference] >=0 || [NextDifference] >= 0 && [Difference] <=0 ||
    SELECTEDVALUE('Table 1'[Date]) = 
    CALCULATE(
        MAX('Table 1'[Date]),
        ALL('Table 1'[Date])
    ) ||
    SELECTEDVALUE('Table 1'[Date]) = 
    CALCULATE(
        MIN('Table 1'[Date]),
        ALL('Table 1'[Date])
    ),
    "Black",
    "White"
)

The above MEASURE is to determine if the point is a turning point or both ends of the end point
Enable datalables and apply the measure to value color

Final output

In order for multiple tables to be realized on the same line chart, you need to create relationships between the tables

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[powellar]

Maybe? I can test out that code, but this doesn't appear to be using any of the sample data provided. I am more looking for trends of total poams opened, total poams closed, total poams delayed, over time. So a line chart showing on 1/1 1 poam was opened, on 3/1, now two poams are opened (the one from 1/1 and the one from 3/1), on 6/1 3 poams were opened. so the line trend should go from 1 to 2 then to 3, with dates of 1/1, 3/1, 6/1. Does that make sense?

[lbendlin]

Put your data in a ribbon chart. See if that works.

[lbendlin]

Not clear what the message is that you want to convey. Please elaborate.

[powellar]

I am trying to create trend graphs over time. Ex. Total POAMs opened over a period of time, total delayed POAMS over a period of time, total completed POAMs over a period of time. I provided an example trend graph that I made in Excel, but can't figure out how to do what I can do easily in excel, in powerBI.