Reply
bye4now
Regular Visitor
Partially syndicated - Outbound

adding a fiscal month/year column

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

Syndicated - Outbound

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

 

Thanks

View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

Syndicated - Outbound

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?

v-lionel-msft_0-1598927417813.png

 

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.

 

jairoaol
Impactful Individual
Impactful Individual

Source Community: Power BI Spanish
Syndicated - Inbound

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/

ryan_mayu
Super User
Super User

Syndicated - Outbound

@bye4now 

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

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

 

Thanks

Syndicated - Outbound

@bye4now 

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)