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

Hi Anonymous ,

It this what you want to achieve?

Here first 4 rows return zeroes as there are only 4 months present out of 12 in 2019. Others return your fiscal year.

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.