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

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.

 

  • 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 so mutch it's perfect! do you have any tips to improve myself at power bi?

    Thank you!!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    ERD Thank you ! I just got an other question, in your previous code what if I want an other filter than date. For exemple I got a column called name and I would like to filter by name. It doesn't work.

    I try to replace the T[Date] of all selected in my column by T[Name] but it put me 0 all over the column.

    If I add it like this 

    ALLSELECTED(T[DATE],T[Column2])
    It doesn't work and put me the year even if there is only one month in the year.
    Thank you!!
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you it's perfect, 

    Have a really nice day