Forum Discussion
Calendar Table Issues - all visuals blank
Please help, I am SO lost. I have managed to get by without a calendr table thus far, but I need to do some time intelligence functions for a custom fiscal year period which necessitates having one.
I have therefore created one with a date key, all the relevant fiscal columns, and related it to my main fact table by the Date Submitted column. All the visuals I had done previously using the dates in the fact table are now showing blank or cannot be displayed.
My questions are these:
1. Once I introduce a calendar table, do I need to go back and replace the dates in ALL existing visuals to use Calendar Table dates instead of date columns from the fact table, or can I chop and change?
2. Is it because I have set the date type of both to date/time (because I need the time) and I don't have a row for every single 'time' in my calendar table, therefore it can't match with the dates in my fact table? Ie. for 29th April I will have in the calendar 29/04/2020 00:00, but some of my records in the fact table might have Date Submitted of 29/04/2020 14:35
3. If I can't get this to work, how else can I do YTD calculations for a custom year without needing a calendar table?! What I need is so simple but this issue is making it so complex! I just need this year YTD count of applications submitted (year is 31/08 - 30/08), and last year ytd count of applications submitted for comparison.
Help please!! parry2k are you able to help?!
4 Replies
Gingerjeans88 , In case you date column has timestamp. Create a new date column and join that date with that
Date Submitted1 =[Date Submitted].date
Date Submitted Time = [Date Submitted].time
Join Date Submitted1 with date table.
For the time you can use time table -https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
refer
YTD : https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
QTD :https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839Hi there amitchandak
Thank you SO much for the speedy reply.
However, I am not sure I follow?
The date column in my fact table is date/time format and it already has a time value. Would I still need to do this?
Hi Gingerjeans88 ,
Please post the sample fact table.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
