Make measure to pick auto business year without hardcode it

[mh2587]

Hi 

I hope you people are doing well,

I want to change the following dax to get 2021/22 year when I use page filter 2020/21 

Note : The Year Description Coulmn is Type of Text

Revenue 2021/22 = CALCULATE ( SUM ( SALES[Sales] ),

FILTER ( 'CALENDAR',

'CALENDAR'[Year Description] == "2021/22"

))

[parry2k]

@mh2587 did you tried dateadd function, I'm not sure what you have tried so far.

[mh2587]

Yup i tried there is a difference in the result As the dateadd function works better on Normal Calendar but didn't as expected on Business Calendar 

[v-stephen-msft]

Hi @mh2587 ,

What is your 21 for 2020/21, is it the week number?

Can you provide sample data and expected results?

A virtual data model and expected results are sufficient.

Best Regards,

Stephen Tao

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

[parry2k]

@mh2587 it will, it is not using year in the syntax but it uses date column.

[mh2587]

Here is one thing more the I am using business calendar not the english calendar , In business calendar the June is first month so it will be difficult for me , how to figure out

[parry2k]

@mh2587 or this, assuming your calendar table is marked as a date table.

Revenue 2021/22 = CALCULATE ( SUM ( SALES[Sales] ),
DATEADD('Calendar'[Date],1,YEAR)
 )

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[mh2587]

The dateadd function will  not work here as Year description data type is text 

[parry2k]

@mh2587 interesting, try this:

Revenue 2021/22 = CALCULATE ( SUM ( SALES[Sales] ),
ALL('Calendar'),
'CALENDAR'[Year Description] == "2021/22"
 )

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@mh2587 change it to this:

Revenue 2021/22 = CALCULATE ( SUM ( SALES[Sales] ),
'CALENDAR'[Year Description] == "2021/22"
 )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[mh2587]

It give same result I want when take the page level filter 20/21 the result should give me the next year value i.e 21/22