topic Re: Fiscal week on non-standard date table in DAX Commands and Tips

Fiscal week on non-standard date table

Nurry90 — Thu, 29 Oct 2020 23:11:12 GMT

Hi All,

I am working with a non-standard date table in Power BI. Usually our fiscal year is July to June, but sometimes week 1 of the fiscal year actually starts in June.

When doing my calculations at the minute, all sales for week 1 are comeing into June sales, which is not accurate when it comes to June sales. I want week 1 (when in June, month 6) to fall into sales in July.

I have tried to work with my m code in the advanced editor, on the date table to manipulate, if wk1 and month = June(month 6), then override to =July(or month 7), else June. But this is giving me 2x July months in my month list.

Does anyone have any suggestions? Mcode manipulation was the 1st thing I thought of, but happy if there is a DAX caculation too.

Thanks,

Noelle.

Re: Fiscal week on non-standard date table

wdx223_Daniel — Fri, 30 Oct 2020 00:17:42 GMT

it's maybe better to provide your sample data and the calculation logic.

Re: Fiscal week on non-standard date table

Nurry90 — Fri, 30 Oct 2020 01:22:44 GMT

Hi wdx223_Daniel ,

It won't let me load my sample pbix file. Sorry I am new here, what should I do? I have attached a screenshot of what my data is showing.

Re: Fiscal week on non-standard date table

Nurry90 — Fri, 30 Oct 2020 01:23:33 GMT

Re: Fiscal week on non-standard date table

amitchandak — Fri, 30 Oct 2020 01:34:53 GMT

Nurry90 , for the calendar of your choice

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

For FY Week - https://www.youtube.com/watch?v=euIC0dgGTNM

or Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3-5-Power-BI/ba-p/1187502