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

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.

    • ERD's avatar
      ERD
      Community Champion
      5 years ago

      Anonymous ,

      *updated

      Oh, in this case there is a small change:

      ALL(T[Date]) -> ALLSELECTED(T[Date])

       

      #FY = 
VAR currentYear = IF(MONTH(SELECTEDVALUE(T[Date])) < 4, YEAR(SELECTEDVALUE(T[Date])) - 1,YEAR(SELECTEDVALUE(T[Date])))
VAR monthsInCurrentYear = 
COUNTAX(
    FILTER(
        ALLSELECTED(T[Date]), 
        IF(MONTH(T[Date]) < 4, YEAR(T[Date]) - 1,YEAR(T[Date])) = currentYear), 
    MONTH(T[Date]))
RETURN
IF(monthsInCurrentYear = 12,
    currentYear,
    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

        Thank you so mutch it's perfect! do you have any tips to improve myself at power bi?

        Thank you!!!