cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

Good morning

newbie here!

I have a data file, that contains daily records of transactions, I want to insert a column and lookup the date from lets say Column A, and return our filscal year. Then run that function down the 4000 or so rows.

eg:

Column A  =  01/08/2019

Comumn B =  Data

Comumn C = Data

Column D  New Column - return a value of 2020-21

I want to do this in Power BI with a DAX function I guess

Thanks

Jill

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Let me try that one out, I also  found a DAX function - its quite long, but I will see which one works.

Thanks

5 REPLIES 5
Highlighted
Super User II

do you have a fiscal year table? if you have, you can try to use LOOKUPVALUE.

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Proud to be a Super User!

Highlighted
Regular Visitor

Let me try that one out, I also  found a DAX function - its quite long, but I will see which one works.

Thanks

Highlighted
Super User II

what's the logic of calculating the column D? 21 means the week number? when does your fiscal year starts? you'd better provide more detailed info to get a proper solution.

Proud to be a Super User!

Highlighted
Resolver II

I recommend that you calculate the fiscal year, fiscal month and so on, do it in the date table and not in the movement table.

the column calculation to show 2020-21 will be performed by Query and not by DAX.

on the internet you can find many examples of date tables, such as the following

http://blog.blauservices.com/?p=1986

https://www.dataxbi.com/blog/2019/08/20/crear-un-calendario-en-power-query/

Highlighted
Community Support

Hi @bye4now ,

What is the relationship between financial date and natural date?
Is there a fixed number of days difference between the two?

Like this?

Best regards,
Lionel Chen

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

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors