Forum Discussion

jayasurya_prud's avatar
jayasurya_prud
Advocate III
3 years ago
Solved

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 Val...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.           

     

jayasurya_prud's avatar
jayasurya_prud
Advocate III
3 years ago

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.
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.