Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Date Year Functions in DAX Issue

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.

 

1 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.           

 

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @jayasurya_prud ,

 

You can create a measure as 

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

vstephenmsft_0-1675663979479.png

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

vstephenmsft_2-1675664110690.png

 

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

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_IDYear_StringYear_numno of yearsGradNot Active
12022 - 2320221yesYes
22021 - 2220212nono
32020 - 2120203yesYes
42019 - 2020194nono
52018 - 1920185yesYes
62017 - 1820176nono
72022 - 2320221yesYes
82021 - 2220212nono
92020 - 2120203yesYes
102019 - 2020194nono
112018 - 1920185yesYes
122017 - 1820176nono
132022 - 2320221yesYes
142021 - 2220212nono
152020 - 2120203yesYes
162019 - 2020194nono
172018 - 1920185yesYes
182017 - 1820176nono
192022 - 2320221yesYes
202021 - 2220212nono
212020 - 2120203yesYes
222019 - 2020194nono
232018 - 1920185yesYes
242017 - 1820176nono
252022 - 2320221yesYes
262021 - 2220212nono
272020 - 2120203yesYes
282019 - 2020194nono
292018 - 1920185yesYes
302017 - 1820176nono
312022 - 2320221yesYes
322021 - 2220212nono
332020 - 2120203yesYes
342019 - 2020194nono
352018 - 1920185yesYes
362017 - 1820176nono
372022 - 2320221yesYes
382021 - 2220212nono
392020 - 2120203yesYes
402019 - 2020194nono
412018 - 1920185yesYes
422017 - 1820176nono
432022 - 2320221yesYes
442021 - 2220212nono
452020 - 2120203yesYes
462019 - 2020194nono
472018 - 1920185yesYes
482017 - 1820176nono
492022 - 2320221yesYes
502021 - 2220212nono
512020 - 2120203yesYes
522019 - 2020194nono
532018 - 1920185yesYes
542017 - 1820176nono

 

 

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
Super User
Super User

@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.

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

 

S_IDYear_StringYear_numno of yearsGradNot Active
12022 - 2320221yesYes
22021 - 2220212nono
32020 - 2120203yesYes
42019 - 2020194nono
52018 - 1920185yesYes
62017 - 1820176nono
72022 - 2320221yesYes
82021 - 2220212nono
92020 - 2120203yesYes
102019 - 2020194nono
112018 - 1920185yesYes
122017 - 1820176nono
132022 - 2320221yesYes
142021 - 2220212nono
152020 - 2120203yesYes
162019 - 2020194nono
172018 - 1920185yesYes
182017 - 1820176nono
192022 - 2320221yesYes
202021 - 2220212nono
212020 - 2120203yesYes
222019 - 2020194nono
232018 - 1920185yesYes
242017 - 1820176nono
252022 - 2320221yesYes
262021 - 2220212nono
272020 - 2120203yesYes
282019 - 2020194nono
292018 - 1920185yesYes
302017 - 1820176nono
312022 - 2320221yesYes
322021 - 2220212nono
332020 - 2120203yesYes
342019 - 2020194nono
352018 - 1920185yesYes
362017 - 1820176nono
372022 - 2320221yesYes
382021 - 2220212nono
392020 - 2120203yesYes
402019 - 2020194nono
412018 - 1920185yesYes
422017 - 1820176nono
432022 - 2320221yesYes
442021 - 2220212nono
452020 - 2120203yesYes
462019 - 2020194nono
472018 - 1920185yesYes
482017 - 1820176nono
492022 - 2320221yesYes
502021 - 2220212nono
512020 - 2120203yesYes
522019 - 2020194nono
532018 - 1920185yesYes
542017 - 1820176nono

 

 

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.

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.           

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.