Time intelligence functionality with imported calendar?

[Anonymous]

Hello, first time posting. I started working with Power BI in my organization to advance our capabilities but I am teaching myself as I go so I keep hitting roadblocks. Hoping someone here can help me out with my latest problem. I have a client that has a unique fiscal period layout (4-5-4) and I have a DAX made calendar that has all the correct offsets and whatnot but I cannot figure out how to make a formula that works with their strange fiscal periods. So I created a calendar table in excel with contiguous dates and hardcoded the periods into that, the only problem I am facing now is that I can't figure out how to set "today" as a variable in the imported sheet so that I can write the correct formulas to get week, period, month, quarter and yearly offsets for my time intelligence functions to work. If someone could please help me figure out either how to get the period structure into the DAX calendar table I have, OR how to get the "today" and offsets to work in my imported excel calendar that would be GREATLY appreciated! THANK YOU!

[v-xicai]

Hi  @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous]

Thank you! Sorry about not responding earlier, just working through the solution and got deep into it. Got the 4-5-4 periodized calendar sorted and now having an issue figuring out LY (year over year) calculations for the WM periods and getting that to work with my drills and whatnot. I think I may have a solution but will post if again (with examples) if I can't figure it out.

Thanks again for your help and sorry for the tardy reply!

[v-xicai · Accepted solution]

Hi @Anonymous  ,

Form the description, you have fiscal period layout (4-5-4), which seems to be related to the special ISO week-numbering year.

You may refer to the article to learn more about the related DAX calculations:

 https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/,

https://fbro.wordpress.com/2013/02/07/powerpivot-excel2013-table-temps-universelle-pour-vos-pocs/.

Best Regards,

Amy

Community Support Team _ Amy

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

[Greg_Deckler]

Not fully following this around "today". I would think that TODAY() is today in any calendar with dates in it. 

But, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

@Greg_Deckler Ok, that makes sense about TODAY([date]), I guess what I am having trouble with then is how to insert a column into my imported calendar that gives me, for example, the offset for each day counting backward from 0 for past dates, and forwards from 0 for future dates with today being 0. Once I get that I should be able to figure it out for Period, month, quarter, and year offset by tweaking the formula. Hopefully, that makes more sense. Again, I am very new to this so if there is something simple I am missing please just point it out.