How to display multiple custom date conditions in a single visual

[Anonymous]

Hello,

I have the following example data:

Date	Sale	Cost
1/1/2020	53	7
1/15/2020	78	21
2/1/2020	31	12
2/15/2020	84	5
3/1/2020	53	20
3/15/2020	31	18
4/1/2020	55	7
4/15/2020	96	6
5/1/2020	69	8
5/15/2020	33	15
6/1/2020	54	17
6/15/2020	46	18
7/1/2020	65	18
7/15/2020	84	22
8/1/2020	35	13
8/15/2020	52	10
9/1/2020	97	9
9/15/2020	88	13
10/1/2020	46	21
10/15/2020	72	9
11/1/2020	97	16
11/15/2020	51	19
12/1/2020	35	7
12/15/2020	71	5
1/1/2021	90	6
1/15/2021	75	20
2/1/2021	46	22
2/15/2021	89	21
3/1/2021	67	21
3/15/2021	88	9
4/1/2021	92	20
4/15/2021	95	7
5/1/2021	88	6
5/15/2021	88	18
6/1/2021	31	6
6/15/2021	48	19

I want to display 3 specific custom date filters on a scatter plot. For example I want to to display the following date filters:

1/1/2020 - 12/31/2020
4/1/2020 - 3/31/2021
7/1/2020 - 6/30/2021

I understand that there will be overlaps in the data, but I want 3 different data markers to be displayed on the scatterplot. Right now I can only achieve individual quarters to be displayed, instead of the year long distinction that I need.

I want the visual to breakdown the lines as so:

1st line: 

1/1/2020	53	7
1/15/2020	78	21
2/1/2020	31	12
2/15/2020	84	5
3/1/2020	53	20
3/15/2020	31	18
4/1/2020	55	7
4/15/2020	96	6
5/1/2020	69	8
5/15/2020	33	15
6/1/2020	54	17
6/15/2020	46	18
7/1/2020	65	18
7/15/2020	84	22
8/1/2020	35	13
8/15/2020	52	10
9/1/2020	97	9
9/15/2020	88	13
10/1/2020	46	21
10/15/2020	72	9
11/1/2020	97	16
11/15/2020	51	19
12/1/2020	35	7
12/15/2020	71	5

2nd line:

4/1/2020	55	7
4/15/2020	96	6
5/1/2020	69	8
5/15/2020	33	15
6/1/2020	54	17
6/15/2020	46	18
7/1/2020	65	18
7/15/2020	84	22
8/1/2020	35	13
8/15/2020	52	10
9/1/2020	97	9
9/15/2020	88	13
10/1/2020	46	21
10/15/2020	72	9
11/1/2020	97	16
11/15/2020	51	19
12/1/2020	35	7
12/15/2020	71	5
1/1/2021	90	6
1/15/2021	75	20
2/1/2021	46	22
2/15/2021	89	21
3/1/2021	67	21
3/15/2021	88	9

3rd line:

7/1/2020	65	18
7/15/2020	84	22
8/1/2020	35	13
8/15/2020	52	10
9/1/2020	97	9
9/15/2020	88	13
10/1/2020	46	21
10/15/2020	72	9
11/1/2020	97	16
11/15/2020	51	19
12/1/2020	35	7
12/15/2020	71	5
1/1/2021	90	6
1/15/2021	75	20
2/1/2021	46	22
2/15/2021	89	21
3/1/2021	67	21
3/15/2021	88	9
4/1/2021	92	20
4/15/2021	95	7
5/1/2021	88	6
5/15/2021	88	18
6/1/2021	31	6
6/15/2021	48	19

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

First create a new table:

NewTable = 
VAR _a = { "1/1/2020 - 12/31/2020", "4/1/2020 - 3/31/2021", "7/1/2020 - 6/30/2021" }
VAR _b =
    CROSSJOIN ( _a, VALUES ( 'Table'[Date] ) )
VAR _c =
    FILTER (
        _b,
        [Date]
            >= DATE ( RIGHT ( LEFT ( [Value], FIND ( "-", [Value] ) - 2 ), 4 ), LEFT (
                LEFT ( [Value], FIND ( "-", [Value] ) - 2 ),
                FIND ( "/", LEFT ( [Value], FIND ( "-", [Value] ) - 2 ) ) - 1
            ), MID (
                LEFT ( [Value], FIND ( "-", [Value] ) - 2 ),
                FIND ( "/", [Value], FIND ( "/", [Value], 1 ) ) + 1,
                FIND ( "/", [Value], 1 + FIND ( "/", [Value], 1 ) )
                    - FIND ( "/", [Value], 1 ) - 1
            ) )
            && [Date]
                <= DATE ( RIGHT ( RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ), 4 ), LEFT (
                    RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ),
                    FIND ( "/", RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ) ) - 1
                ), MID (
                    RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ),
                    FIND (
                        "/",
                        RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ),
                        FIND ( "/", [Value], 1 )
                    ) + 1,
                    FIND (
                        "/",
                        RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ),
                        1
                            + FIND ( "/", RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ), 1 )
                    )
                        - FIND ( "/", RIGHT ( [Value], LEN ( [Value] ) - FIND ( "-", [Value] ) - 1 ), 1 ) - 1
                ) )
    )
RETURN
    _c

Output:

Then apply these two measrues to a matrix visual:

Sale = SUMX(FILTER('Table',[Date] in SELECTCOLUMNS('NewTable',"Date",[Date])),[Sale])

Cost = SUMX(FILTER('Table',[Date] in SELECTCOLUMNS('NewTable',"Date",[Date])),[Cost])

Final output:

Best Regards,

Jianbo Li

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