Forum Discussion

Mannai's avatar
Mannai
Helper I
4 years ago

Dax Filter not calculating correctly

Hello everyone ,
I have total of leaves for key people in the last 12 months so to calculate it i use this measure 

Total key people leaves = CALCULATE([Total leaves],FILTER('KP List','KP List'[Type]="KP"),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

  the problem is that this measure calculte the key people even if he was only i key people in 2021 and left the company in 2022 where he is no longer a kp. 
so to fix it i added another filter to dax :

Total key people leaves= CALCULATE([Total leaves],FILTER('KP List','KP List'[Type]="KP"),FILTER('KP List',YEAR('KP List'[Année]=YEAR(MAX('total employee'[leave_date])))),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

but it didnt fix my problem.


any ideas please ?

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Mannai , NOt very clear if you need it only for this year

    you can use like

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    Simply based on today

     

    YTD Today =
    var _min = eomonth(today(),-1*month(today()))+1
    var _max = today()
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    YTD Yesterday =
    var _min = eomonth(today(),-1*month(today()))+1
    var _max = today() -1
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    • Mannai's avatar
      Mannai
      Helper I
      4 years ago

      Hello thanks for your reply. 

      the calculation i want to make is to calculate only the total where a field named "year" is the same in the other field named "date" (year is "YYYY" type and "date" is "DD/MM/AAAA"
      Thank you