How do I transform date that are in calendar year into Government fiscal year

[frenchiedc87]

Bonjour

 I have a data set with date organized by calendar year, I need to transform those date in Government fiscal year.

2020 government fiscal year is:  10/01/2019 to 09/30/2020

Thanks

[parry2k]

@frenchiedc87 there are many posts on how to create a custom fiscal year, here is link to one. 

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

[frenchiedc87]

great code, but I cannot apply it to the direct query mode

do you have another option

[parry2k]

@frenchiedc87 Not sure why, you can have dual storage mode in your model, with import and direct query? 

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

[frenchiedc87]

the report has to be dynamic and therefore in direct query. So I am looking for a solution that will work with this contruct

Thanks

[parry2k]

@frenchiedc87 in that case you have to take your fiscal calendar to your data source (backend) and then use it from there. 

Although I'm not sure what you mean by dynamic, it will be dynamic whatever logic you put. I'm a little bit lost.

[frenchiedc87]

by dynamic I meant that that the data is pulled through Direct Query and is updated live.

I believe if I switch to direct import I will lose the ability to update the data in a continuing way

I thought there was a way to transform the date while maintaining the Direct Query feed

[parry2k]

@frenchiedc87 you will not lose anything if you move a table from a direct query to import