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.           

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
jayasurya_prud
Advocate III
3 years ago

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.