Date Year Functions in DAX Issue

[jayasurya_prud]

Hi! 
I am working on a Dax to have a year filter dynamically. 

I have two columns to consider for Date

1. Year_String = Example Value = "2022 - 23". Data Type Strimg

2. Year_Num  = Example Value  = 2022 . Data Type int.

In my dax, I need build the logic with the difference of present year (2023 - Year_Num ) = 1 (Example - 2023 - 2022 = 1)

It should iterate with differnt measures as 

(2023 - Year_Num ) = 2 (Example - 2023 - 2021 = 2)

(2023 - Year_Num ) = 3 (Example - 2023 - 2020 = 3)

(2023 - Year_Num ) = 4 (Example - 2023 - 2019 = 4)

(2023 - Year_Num ) = 5 (Example - 2023 - 2018 = 5)

(2023 - Year_Num ) = 6 (Example - 2023 - 2017 = 6)

I am even fine, if we can use the Year String column. 

Please help me with the Dax. 

I will be using this Dax as the filter in the Table. I will be creating multiple var in the DAX. So the Year filter should be going well with that.

[v-stephen-msft]

Hi @jayasurya_prud ,

You can create a measure as 

Expected = var _presentyear=YEAR(TODAY())
return _presentyear-SUM('Table'[Year_Num])

If you want to use Year_String column to calculate, please refer to this measure.

Expected2 = var _presentyear=YEAR(TODAY())
var _year=VALUE(LEFT(MAX('Table'[Year_String]),4))
return _presentyear-_year

Best Regards,

Stephen Tao

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

[jayasurya_prud]

Hi! Apprecoat the reply. Thanks for the dax. But this is not helping me to use the date function in countrows filter.  Please find the detail below.

Hi! Thanks for replying with the dax. I am afraid that this is not working. Here is the sample data

S_ID	Year_String	Year_num	no of years	Grad	Not Active
1	2022 - 23	2022	1	yes	Yes
2	2021 - 22	2021	2	no	no
3	2020 - 21	2020	3	yes	Yes
4	2019 - 20	2019	4	no	no
5	2018 - 19	2018	5	yes	Yes
6	2017 - 18	2017	6	no	no
7	2022 - 23	2022	1	yes	Yes
8	2021 - 22	2021	2	no	no
9	2020 - 21	2020	3	yes	Yes
10	2019 - 20	2019	4	no	no
11	2018 - 19	2018	5	yes	Yes
12	2017 - 18	2017	6	no	no
13	2022 - 23	2022	1	yes	Yes
14	2021 - 22	2021	2	no	no
15	2020 - 21	2020	3	yes	Yes
16	2019 - 20	2019	4	no	no
17	2018 - 19	2018	5	yes	Yes
18	2017 - 18	2017	6	no	no
19	2022 - 23	2022	1	yes	Yes
20	2021 - 22	2021	2	no	no
21	2020 - 21	2020	3	yes	Yes
22	2019 - 20	2019	4	no	no
23	2018 - 19	2018	5	yes	Yes
24	2017 - 18	2017	6	no	no
25	2022 - 23	2022	1	yes	Yes
26	2021 - 22	2021	2	no	no
27	2020 - 21	2020	3	yes	Yes
28	2019 - 20	2019	4	no	no
29	2018 - 19	2018	5	yes	Yes
30	2017 - 18	2017	6	no	no
31	2022 - 23	2022	1	yes	Yes
32	2021 - 22	2021	2	no	no
33	2020 - 21	2020	3	yes	Yes
34	2019 - 20	2019	4	no	no
35	2018 - 19	2018	5	yes	Yes
36	2017 - 18	2017	6	no	no
37	2022 - 23	2022	1	yes	Yes
38	2021 - 22	2021	2	no	no
39	2020 - 21	2020	3	yes	Yes
40	2019 - 20	2019	4	no	no
41	2018 - 19	2018	5	yes	Yes
42	2017 - 18	2017	6	no	no
43	2022 - 23	2022	1	yes	Yes
44	2021 - 22	2021	2	no	no
45	2020 - 21	2020	3	yes	Yes
46	2019 - 20	2019	4	no	no
47	2018 - 19	2018	5	yes	Yes
48	2017 - 18	2017	6	no	no
49	2022 - 23	2022	1	yes	Yes
50	2021 - 22	2021	2	no	no
51	2020 - 21	2020	3	yes	Yes
52	2019 - 20	2019	4	no	no
53	2018 - 19	2018	5	yes	Yes
54	2017 - 18	2017	6	no	no

I want help of date function in the filter. This is the dax I am trying

year_1 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2022 - 23")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2022 - 23")

return counts - grads_attr

this gives me the records of last year. But I have used the year value as Hard code value. I need change this to dynamic.

like wise I am using this formula for many years, 

year_2 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2021 - 22")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2021 - 22")

return counts - grads_attr

year_3 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2020 - 21")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2020 - 21")

return counts - grads_attr

year_4 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2019 - 20")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2019 - 20")

return counts - grads_attr

year_5 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2018 - 19")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2018 - 19")

return counts - grads_attr

year_6 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2017 - 18")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2017 - 18")

return counts - grads_attr

please help me with the dax.

[amitchandak]

@jayasurya_prud , You can have column

max(Table[Year_num]) - [Year_num]

or a meausre

Maxx(allselected(Table),Table[Year_num]) - max(Table[Year_num]) 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jayasurya_prud]

Hi! Thanks for replying with the dax. I am afraid that this is not working. Here is the sample data

S_ID	Year_String	Year_num	no of years	Grad	Not Active
1	2022 - 23	2022	1	yes	Yes
2	2021 - 22	2021	2	no	no
3	2020 - 21	2020	3	yes	Yes
4	2019 - 20	2019	4	no	no
5	2018 - 19	2018	5	yes	Yes
6	2017 - 18	2017	6	no	no
7	2022 - 23	2022	1	yes	Yes
8	2021 - 22	2021	2	no	no
9	2020 - 21	2020	3	yes	Yes
10	2019 - 20	2019	4	no	no
11	2018 - 19	2018	5	yes	Yes
12	2017 - 18	2017	6	no	no
13	2022 - 23	2022	1	yes	Yes
14	2021 - 22	2021	2	no	no
15	2020 - 21	2020	3	yes	Yes
16	2019 - 20	2019	4	no	no
17	2018 - 19	2018	5	yes	Yes
18	2017 - 18	2017	6	no	no
19	2022 - 23	2022	1	yes	Yes
20	2021 - 22	2021	2	no	no
21	2020 - 21	2020	3	yes	Yes
22	2019 - 20	2019	4	no	no
23	2018 - 19	2018	5	yes	Yes
24	2017 - 18	2017	6	no	no
25	2022 - 23	2022	1	yes	Yes
26	2021 - 22	2021	2	no	no
27	2020 - 21	2020	3	yes	Yes
28	2019 - 20	2019	4	no	no
29	2018 - 19	2018	5	yes	Yes
30	2017 - 18	2017	6	no	no
31	2022 - 23	2022	1	yes	Yes
32	2021 - 22	2021	2	no	no
33	2020 - 21	2020	3	yes	Yes
34	2019 - 20	2019	4	no	no
35	2018 - 19	2018	5	yes	Yes
36	2017 - 18	2017	6	no	no
37	2022 - 23	2022	1	yes	Yes
38	2021 - 22	2021	2	no	no
39	2020 - 21	2020	3	yes	Yes
40	2019 - 20	2019	4	no	no
41	2018 - 19	2018	5	yes	Yes
42	2017 - 18	2017	6	no	no
43	2022 - 23	2022	1	yes	Yes
44	2021 - 22	2021	2	no	no
45	2020 - 21	2020	3	yes	Yes
46	2019 - 20	2019	4	no	no
47	2018 - 19	2018	5	yes	Yes
48	2017 - 18	2017	6	no	no
49	2022 - 23	2022	1	yes	Yes
50	2021 - 22	2021	2	no	no
51	2020 - 21	2020	3	yes	Yes
52	2019 - 20	2019	4	no	no
53	2018 - 19	2018	5	yes	Yes
54	2017 - 18	2017	6	no	no

I want help of date function in the filter. This is the dax I am trying

year_1 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2022 - 23")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2022 - 23")

return counts - grads_attr

this gives me the records of last year. But I have used the year value as Hard code value. I need change this to dynamic.

like wise I am using this formula for many years, 

year_2 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2021 - 22")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2021 - 22")

return counts - grads_attr

year_3 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2020 - 21")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2020 - 21")

return counts - grads_attr

year_4 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2019 - 20")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2019 - 20")

return counts - grads_attr

year_5 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2018 - 19")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2018 - 19")

return counts - grads_attr

year_6 =

var counts = CALCULATE(COUNTROWS(Table),Table[year_string] = "2017 - 18")

var grads_attr = CALCULATE(COUNTROWS(table),table[no of grad] = 1,not(table[not active] in {"yes"}), table[year_string] = "2017 - 18")

return counts - grads_attr

please help me with the dax.

[v-stephen-msft · Accepted solution]

Hi @jayasurya_prud ,

Are table and table the same table? There is no [no of grad] in the sample data, and I try to make you understand how to dynamically group calculations.

Since you want dynamic results. If it's a calculated column, please try

year column = 
var counts = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Year_String]=MAX('Table'[Year_String])))
var grads_attr =CALCULATE(COUNTROWS('Table'),FILTER('Table',[no of years] = 1&&[Not Active]<>"Yes"&&[Year_String] =MAX('Table'[Year_String])))
return counts - grads_attr

If it's a measure, please try

year measure = 
var counts = CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),[Year_String]=MAX('Table'[Year_String])))
var grads_attr =CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),[no of years] = 1&&[Not Active]<>"Yes"&&[Year_String] =MAX('Table'[Year_String])))
return counts-grads_attr

We often use FILTER(ALLSELECTED('Table'),[Year_String]=MAX('Table'[Year_String]) to group in measures and use FILTER('Table',[Year_String]=EARLIER('Table'[Year_String]) to group in calcualted columns.

Best Regards,

Stephen Tao

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