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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Yes exactly thank you just it must have the 12 months from april 2020 to mars 2019 and not from january to december. But It's already great! thank you! Can you send me the code pls? 

ERD's avatar
ERD
Community Champion
5 years ago

Anonymous ,

The code from the example above is (calculated column):

FY_cln = 
VAR currentYear = YEAR(T[Date])
VAR monthsInCurrentYear = 
COUNTAX(FILTER(ALL(T[Date]), YEAR(T[Date]) = currentYear), MONTH(T[Date]))
RETURN 
IF(monthsInCurrentYear = 12,
    IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date])),
    0
)

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

    just can i do it in a mesure? Because if i change the filter in the overview, it doesn't work anymore

    • ERD's avatar
      ERD
      Community Champion
      5 years ago

      Anonymous ,

      In case of 12 months per fiscal year, it might be not the fanciest way (I don't know your model, etc.), but try this:

      Calculated column:

      FY_cln = 
VAR currentYear = IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date]))
VAR monthsInCurrentYear = 
COUNTAX(
    FILTER(
        ALL(T[Date]), 
        IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date])) = currentYear), 
    MONTH(T[Date]))
RETURN
IF(monthsInCurrentYear = 12,
    IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date])),
    0
)

      Measure:

      #FY = 
VAR currentYear = IF(MONTH(SELECTEDVALUE(T[Date])) < 4, YEAR(SELECTEDVALUE(T[Date])) - 1,YEAR(SELECTEDVALUE(T[Date])))
VAR monthsInCurrentYear = 
COUNTAX(
    FILTER(
        ALL(T[Date]), 
        IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date])) = currentYear), 
    MONTH(T[Date]))
RETURN
IF(monthsInCurrentYear = 12,
    IF(MONTH(SELECTEDVALUE(T[Date])) < 4, YEAR(SELECTEDVALUE(T[Date])) - 1,YEAR(SELECTEDVALUE(T[Date]))),
    0
)

       

      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

        Thanks,

        but do you know why even if I change the filter on the report, my measure don't change?

        For exemple, if I put only april to july, as there are not the twelve month, i would like to have 0 and now it still give me the year.