YTD vs YTD 14 days before last datapoint

[Wilm117]

Hi Everybody,

I am working on a report in which I present a number of notifications. I want to see the total number of notifications per day and presented in a YTD format. I also want to show the # YTD notifications 14 days before each date, as shown in the table. Can somebody help me with the right DAX formula? 

Thanks you so much!

Date	# notifications	YTD notifications	YTD notifications - 14 days
1-2-2022		4
2-2-2022		4
3-2-2022	1	5
4-2-2022	1	6
5-2-2022		6
6-2-2022		6
7-2-2022	1	7
8-2-2022		7
9-2-2022		7
10-2-2022	1	8
11-2-2022		8
12-2-2022		8
13-2-2022		8
14-2-2022		8	4
15-2-2022		8	4
16-2-2022		8	5
17-2-2022		8	6
18-2-2022		8	6
19-2-2022		8	6
20-2-2022		8	7
21-2-2022	2	10	7
22-2-2022		10	7
23-2-2022		10	8
24-2-2022	2	12	8
25-2-2022		12	8
26-2-2022		12	8
27-2-2022		12	8
28-2-2022		12	8

[Wilm117]

The calculated column doesn't work, because the example i gave came from a table in power BI and not direcly from the data source. my bad.

The table i presented was derrived from the spreadsheet which is the data source, which can be found below. 

I'd like get the same output, but based on the dataset below. Each notification_ID is unique and i want to see the amount of unique notifications per day. This data is connected to a date table, which i would like to incorporate in my DAX. Is this possible? 

Notification_ID	Date
2022-02-01 08:35-N201	1-2-2022
2022-02-01 08:36-N221	1-2-2022
2022-02-01 08:38-N420	1-2-2022
2022-02-02 11:43-N229	2-2-2022
2022-02-02 15:45-N221	2-2-2022
2022-02-02 21:17-N237	2-2-2022
2022-02-03 09:48-N230	3-2-2022
2022-02-03 11:34-N201	3-2-2022
2022-02-03 14:00-N238	3-2-2022
2022-02-04 07:27-N408	4-2-2022
2022-02-04 12:07-Waterlinie	4-2-2022
2022-02-04 19:33-N204	4-2-2022
2022-02-04 19:37-Waterlinieweg	4-2-2022
2022-02-04 19:39-Waterlinieweg	4-2-2022
2022-02-05 17:33-N233	5-2-2022
2022-02-06 12:54-N226	6-2-2022
2022-02-06 16:05-N237	6-2-2022
2022-02-06 22:10-N210	6-2-2022
2022-02-07 08:20-N230	7-2-2022
2022-02-07 10:12-Amsterdamsestraatweg - Industrieweg	7-2-2022
2022-02-07 11:47-N230	7-2-2022
2022-02-07 14:51-N237	7-2-2022
2022-02-07 15:22-N238	7-2-2022
2022-02-07 17:04-N224	7-2-2022

[FreemanZ · Accepted solution]

hi @Wilm117 

then try to plot a table visual with Date column and two measures like:

YTD =
COUNTROWS(
    FILTER(
        ALL(TableName),
        TableName[Date]<=MAX(TableName[Date])
    )
)

YTD -14 =
COUNTROWS(
    FILTER(
        ALL(TableName),
        TableName[Date]<=MAX(TableName[Date])-14
    )
)

[Wilm117]

Thanks, this worked!

[FreemanZ]

hi @Wilm117 

try to add two calculated columns like:

YTD =
SUMX(
    FILTER(
        TableName,
        TableName[Date]<=EARLIER(TableName[Date])
    ),
    TableName[# notifications]
)

YTD -14 =
SUMX(
    FILTER(
        TableName,
        TableName[Date]<=EARLIER(TableName[Date])-14
    ),
    TableName[# notifications]
)

it worked like: