No Calendar Table? How to add Fiscal Month and Sort in Power BI?
Vložit
- čas přidán 21. 12. 2021
- Thinking you don't need a date table in Power BI? What about Fiscal month? Patrick shows how you could potentially go about adding that without a full date table.
But really, use a central date table 😎
Do you need a date table for time intelligence in Power BI? Nope! (Quick Measures)
• Do you need a date tab...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Calendar #GuyInACube - Věda a technologie
Patrick, this is excellent as normal. The only thing that's missing is to add the very likely requirement of the financial year, for example 2021/22.
Thanks Patrick - I was banging my head on the desk trying to get this work from some other video on the surfweb and you nailed it with an easy solution! Should have come here first - I will next time.
This is the best + easy way to adjust a fiscal year without another mapping table man! Thanks for sharing!
Cool trick 👍 It's like you are extending your table with calendar table's columns.
Thanks 🙏 for sharing Patrick
Patrick, You have saved my job. Thank you so much!
This is really helpful wow Just saved my time. Thanks for sharing🥰
Hi Patrick
Thanks for the tutorial. Exactly what I need.
Thank you Patrick for this. Appreciate it.
I want say thanks this video help me create a correct sort on my date table.
This saved me so much time! Thank you!!!
Thanks Patrick!
Wonderful information as always. I have tried to hunt down everywhere how to do this fiscal month, and yours showed up. Thank you. Now I have a question, is there a way to resort the date. For example, start date of each fiscal month is 22nd. How do I do it with each month having different number of days?
Another great video! I was dealing with this issue just yesterday for an October 1 FY start. However, my challenge was that I have to look back 6 months. So February 2022 is FY22 Period 4 but September 2021 is FY21 Period 12 so sorting wasn’t working for me with that formula as it doesn’t take into account CFY and PFY. I’m a newbie so probably missing something obvious but only way to get it to work was a date table. Thoughts?
Awesome !!!
Hi Patrick, I have a weird request. I have a date column in my dataset. The task is to create 4 separate drop down menus namely 'From Month', 'From Year', 'To Month' and 'To Year' and fetch the data between the two given month-year combo, inclusive of both months. It would be sweet if after selecting in the first two drop down menus, the last two show months and years after that.
Thanks in advance. Would even take suggestions from the community.
Hi guys, love your videos! I'm just wondering how you would deal with fiscal periods that are not consistent? For example, for my company, our fiscal year has periods of 5 weeks, then 4 weeks, then 4 weeks, and that times 3. So you will end up with periods like 6 February to 5 March, etc. I would like to do calculations comparing a fiscal period to the previous one. I know Power BI does have functions like "PREVIOUSMONTH", "NEXTMONTH", "STARTOFMONTH", "ENDOFMONTH" etc, and I would like to be able to use these on fiscal periods in stead of months. Is this possible, and if yes, how?
You saved my life man
When can you NOT have a central date table though? If you have the ability to add calc columns to the base table, you must also have the ability to write a DAX calculated table for the dates?
Totally agree. Then you can solve the fiscal year issue in that same date table
@@kevarnold I have a date table w/fiscal years, etc., but I can't figure out how to get these results using the table. I'm sure I'm over thinking this and making it more complicated than it needs to be.
Hi there I am having some issues with my table and I hope you can help me with it. I am able to show all the months even the months with not data. However, the months are showing in a chronologically order and I want to be sorted by fiscal year. I added a column with the fiscal month number and sort the date by it and that works but now it will not show the months with no data even though I clicked on show items with no data. Could you tell me what I am doing wrong? Thank you for your help!
Hey Patrick I have a question. So I have Data in which it is financial sums but the customer has given me no dates. They just have it as this is the fiscal year totals. How can i create a slicer so that i can put each of those Columns become visible. For example the FY21, FY22,FY23 can be selected by on slicer from the actual titles?
Hi, Patrick. I got a question, you can help me perhaps. When I'm creating a slicer in the report, the default state is a "list", Is it possible to set the default as "dropdown". By customizing theme json file or set some properties via Tabular Editor, I don't know. Thanks in advance.
I have date field in my table.also i have the value for the foolowing columns (Openning Balance, Received,Issued,Ending) for each day.
i want to show the reconciliation summary report for each month. How can i get the first and end date for each month in Power BI?
Grt man
I'm using MONTH(EDATE(Order Date,-9)) for key to month. This helps with adding Year with ease.
I k j
O
O
but if you do it this way how do then calculate month over month in order of fiscal months i have been struggling with this for ever
Which date table do you use that has the correct weeknumbers, and updates itself in the future?
hi, this won't work for me because I have October starting in last monday of september. So 9/30 is fiscal October. I do have a fiscal calendar the company shares. It has the month, year, and the start and end dates for that month. woudl I just do a related fuction Or lookupvalue?
legend
What is an order date???
Patrick... very helpful, but I have an even tougher dilemma. Our Fiscal Year starts on a different day every year and we have a table that stores the start date and the stop date of each FY. I would like to create a date table that would assign the proper fiscal year to every day for several years. Any ideas?
Would be useful to know how the first day of your fiscal year is determined.
Could be wrong here, but I can't imagine a scenario where a date table cannot be easily created, not sure what value these types of videos add, focusing on the best practices should be at the centre of focus, not show work around hacks for things that are just plain bad practice.
Could you think of any other way to do this? If not then Power BI is a limited capacity tool
@@Mojochetan You just add a calendar table, check the other comments.
always central calendar table, nothing else
Can you please change your channel name into Crazy PBI with PatAd