Contains date

[Anonymous]

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

[ERD]

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]

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]

@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]

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

[ERD]

@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]

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]

@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]

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

Thank you!!!

[ERD]

Read books and articles, follow blogs, watch videos and practice.

Examples of blogs/channels:

https://www.sqlbi.com/

https://radacad.com/blog

https://www.youtube.com/c/CurbalEN

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

@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!!

[ERD · Accepted solution]

@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]

Thank you it's perfect, 

Have a really nice day

[amitchandak]

@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]

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