Adding more syntax to the M Query

[Marshmallow]

Hi, 

I found the following syntax which is working for me: 

let a=#date(Date.Year([Date_Min_Signed]),7,1),
b=#date(Date.Year([Date_Min_Signed])+1,6,30)
in if [Date_Min_Signed]>=a and [Date_Min_Signed]<=b then "FY"&Text.End(Number.ToText(Date.Year([Date_Min_Signed])),2) else "FY"&Text.End(Number.ToText(Date.Year([Date_Min_Signed])-1),2)

Using this formula, the output is:

Date	Financial Year
4/7/2023	FY23
6/3/2023	FY22

However I would like the output to be: 

Date	Financial Year
4/7/2023	FY23-24
6/3/2023	FY22-23

Can someone please help?

[MasonMA · Accepted solution]

@Marshmallow 

Hi, you simply need to append the second year in the format YY, which is always one year after the base year. With adjusted M codes below: 

let
    a = #date(Date.Year([Date_Min_Signed]), 7, 1),
    b = #date(Date.Year([Date_Min_Signed]) + 1, 6, 30),
    fyStartYear = if [Date_Min_Signed] >= a and [Date_Min_Signed] <= b then Date.Year([Date_Min_Signed]) else Date.Year([Date_Min_Signed]) - 1,
    fyEndYear = fyStartYear + 1,
    result = "FY" & Text.End(Text.From(fyStartYear), 2) & "-" & Text.End(Text.From(fyEndYear), 2)
in
    result

[grazitti_sapna · Accepted solution]

Hi @Marshmallow 

Please try the below M code:

let
a = #date(Date.Year([Date_Min_Signed]), 7, 1),
b = #date(Date.Year([Date_Min_Signed]) + 1, 6, 30),
fyStartYear = if [Date_Min_Signed] >= a and [Date_Min_Signed] <= b then Date.Year([Date_Min_Signed]) else Date.Year([Date_Min_Signed]) - 1,
fyEndYear = fyStartYear + 1,
result = "FY" & Text.End(Text.From(fyStartYear), 2) & "-" & Text.End(Text.From(fyEndYear), 2)
in
result


 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[v-sgandrathi]

Hi @Marshmallow,

Thank you for being a part of the Microsoft Fabric Community.

@MasonMA Thank you for providing the answer along with screenshots of the output.

If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[v-sgandrathi]

Hi @Marshmallow,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Looking forward to your response
Thank you.

[Ashish_Mathur]

Hi,

Just in case you prefer a DAX solution, write this calculated column formula in the Calendar table

FY = if(month(Calendar[date])>=7,"FY"&right(year(calendar[date]),2)&"-"&right(year(calendar[date])+1,2),"FY"&right(year(calendar[date])-1,2)&"-"&right(year(calendar[date]),2))

Hope this helps.

[Ashish_Mathur]

Hi,

Just in case you prefer a DAX solution, write this calculated column formula in the Calendar table

FY = if(month(Calendar[date])>=7,"FY"&right(year(calendar[date]),2)&"-"&right(year(calendar[date])+1,2),"FY"&right(year(calendar[date])-1,2)&"-"&right(year(calendar[date]),2))

Hope this helps.

[MasonMA · Accepted solution]

@Marshmallow 

Hi, you simply need to append the second year in the format YY, which is always one year after the base year. With adjusted M codes below: 

let
    a = #date(Date.Year([Date_Min_Signed]), 7, 1),
    b = #date(Date.Year([Date_Min_Signed]) + 1, 6, 30),
    fyStartYear = if [Date_Min_Signed] >= a and [Date_Min_Signed] <= b then Date.Year([Date_Min_Signed]) else Date.Year([Date_Min_Signed]) - 1,
    fyEndYear = fyStartYear + 1,
    result = "FY" & Text.End(Text.From(fyStartYear), 2) & "-" & Text.End(Text.From(fyEndYear), 2)
in
    result

[FBergamaschi]

This is a Power Query question, please post it in the right forum

https://community.fabric.microsoft.com/t5/Power-Query/bd-p/power-bi-services

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI