topic Re: YTD vs YTD 14 days before last datapoint in DAX Commands and Tips

YTD vs YTD 14 days before last datapoint

Anonymous — Wed, 05 Apr 2023 12:49:21 GMT

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

Re: YTD vs YTD 14 days before last datapoint

FreemanZ — Wed, 05 Apr 2023 13:21:43 GMT

hi Anonymous

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:

Re: YTD vs YTD 14 days before last datapoint

Anonymous — Wed, 05 Apr 2023 14:21:00 GMT

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

Re: YTD vs YTD 14 days before last datapoint

FreemanZ — Wed, 05 Apr 2023 14:29:52 GMT

hi Anonymous

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 ) )

Re: YTD vs YTD 14 days before last datapoint

Anonymous — Wed, 05 Apr 2023 14:54:59 GMT

Thanks, this worked!