I need to use the “Date table” but the date information I extract from our account database is coming as a combination of the “fiscal year – and the period” ex: 2020-2021/05 meaning “August 2020” as our fiscal year is from April 1st to March 31st - This year it is April 1st 2020 until March 31st 2021
Month - Period - Quarters
April 01 Q1
May 02 Q1
June 03 Q1
July 04 Q2
Aug 05 Q2
Sept 06 Q2
Like I said my data is coming like this:
2020-2021/05
2020-2021/06
2020-2021/07
2020-2021/08
But would like to convert it to the last day of the month like below to be able to link it to the “date table”
***Report *** *Convert to:
2020-2021/05 2021-08-31 or August 31, 2021
2020-2021/06 2021-09-30 or September 30, 2021
2020-2021/07 2021-10-31 or October 31 , 2021
2020-2021/08 2021-11-30 or November 30, 2021
While reading other examples I realize that I can modify the date table but I am getting lost – Can you please help me what is the best option there please. How should I update the extended date table to link it with my data? what changes should I make in my data too (inside of the query editor for sure). Thanks in advance for the help.
Hi @Alourdes, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Melissa I watched all the videos but I am still confused. I need to be able to put an extended columns to the “date table” that Brian show us in the course “ultimate beginners guide to PowerBI”. I did watch another video from Brian " How To Create Custom Financial Year Quarters - Power BI" and could add it in my date table but I need to have a filter like this “2019-2020” instead of only the year “2020”
Let me explain: the information you provided to me below I merge it to my “Transactions - Fact table” but I don’t think it is what I should do as I still don’t know how I will be able to filter by
Example “Fiscal Year (2020-2021)”, instead of “Year 2020”
Sorry if I am not clear but if you don’t understand I can’t try again to explain.
But thank you so much for the fast reply, you guys are awesome - I was trying to find out if I can fix it myself before I comment - cheers
This is beginning to sound more like a modelling question… Thing is there are specific rules for the Date dimension tables when it comes to Time Intelligence functions. You can check the documentation here
It’s important that your Date dimension and Fact table share a common Key. However if your fact table isn’t at the same granularity as the Date table that’s not an issue because you don’t have to create a relationship between the two. Alternatively you could use TREATAS for example to deal with that as long as the Date dimension table contains all Date attributes you require to slice and dice your data by.
Hi @Alourdes, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
