Calendar Table Issues - all visuals blank

[Gingerjeans88]

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?!

[amitchandak]

@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-d842063da839

FY Calendar -https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[Gingerjeans88]

Hi 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?

[v-lionel-msft]

Hi @Gingerjeans88 ,

Please post the sample fact table.

Best regards,
Lionel Chen

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

[Gingerjeans88]

Hi @v-lionel-msft  thank you SO much!! I am getting desperate. 

I have mocked up some data, it is in the attache dropbox link - but I have not related the date table to the fact tables, as that is the bit I am hoping you'll help me with. 

If my academic year begins on 31st August, is my '9' for start of academic year parameter incorrect? 

https://www.dropbox.com/s/e04qd29o94ildpq/Sample%20PBIX%20-%20Applications.pbix?dl=0 

In my real pbix I have lots more fact tables all related to one another. 

Many thanks,

Lynn