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 are all the date between april 2019 and mars 2020, (avril/2019,mai/2019, juin 2019, juillet 2019, aout 2019, septembre 2019, octobre 2019, novembre 2019, decembre 2019, janvier 2020, fevrier 2020, mars 2020) it would put me 2019 in the mesure.

If there are also all the month between april 2018 and mars 2019 it would put me 2018.

And this from april 2019 to now.

If there are not all the date between april N and mars N+1 or for the date before april 2019, I would like to have a blank or a 0.

Do you know how to do it pls?

Thank you

Have a nice day

 

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.

14 Replies

  • 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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , As you have date , try a column like 

    Start of year Date= startofyear([Date], "3/31")

     

    Start of year = year(startofyear([Date], "3/31")

     

    or

     

    year = if(month([date])< 4 , year([Date])-1, year([Date]))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Thank you but how can I do the condition that all the 12 month should be present or I will put a 0?