Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Contains date

Hello! I got a bunch of date like this  And I Would like to have the year coresponding of the date column but only if there are all month between april/N to mars/N+1. For exemple if there ar...
Need Help
  • ERD's avatar
    ERD
    5 years ago

    Anonymous ,

    If I understand correctly, your date column doesn't contain different days, just different months per different years.

    Please, try this measure, worked for me with the next data structure:

    #FY2 = 
VAR currentYear = IF(MONTH(SELECTEDVALUE(T[Date])) < 4, YEAR(SELECTEDVALUE(T[Date])) - 1,YEAR(SELECTEDVALUE(T[Date])))
VAR monthsInCurrentYear = 
CALCULATE(
    DISTINCTCOUNT(T[Date]),
    FILTER(
        ALLSELECTED(T), 
        currentYear = IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date]))
    )
)
VAR Result = IF(monthsInCurrentYear = 12,
    currentYear,
    0
)
RETURN Result

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

ERD's avatar
ERD
Community Champion
5 years ago

Anonymous ,

If I understand correctly, your date column doesn't contain different days, just different months per different years.

Please, try this measure, worked for me with the next data structure:

#FY2 = 
VAR currentYear = IF(MONTH(SELECTEDVALUE(T[Date])) < 4, YEAR(SELECTEDVALUE(T[Date])) - 1,YEAR(SELECTEDVALUE(T[Date])))
VAR monthsInCurrentYear = 
CALCULATE(
    DISTINCTCOUNT(T[Date]),
    FILTER(
        ALLSELECTED(T), 
        currentYear = IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date]))
    )
)
VAR Result = IF(monthsInCurrentYear = 12,
    currentYear,
    0
)
RETURN Result

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
5 years ago

Thank you it's perfect, 

Have a really nice day