Matrix problems with calendar

[mohsenask]

I have a basic table with Booking nos., Booking Dates, Departure Dates, Arrival Dates.

I created two calendars; Bkng_Calendar and Arrivals_Calendar. Related them to the relevant dates in the main Table having all data called 'DATA'

I use the matrix visual to build a visualisation that shows me Arrival Dates - MoM and it works fine.

The exact same thing works fine with the Arrival Dates_Calendar connected to the Booking Date in the DATA table.

ARRIVAL CALENDAR related to BOOKING DATE

I connect the Bkng_calendar

However, when i connect the Bkng_calendar and build a visualiation to display the same MoM for Booking dates it gives me blank dates. it doesnt show the year, month, indented level and displays the total sum of bookings rather than the individual month totals

BOOKING CALENDAR related to BOOKINGDATE

Both calendars use the exact same code to create the calendar

Please help

[amitchandak · Accepted solution]

@mohsenask , I forget to ask. Does the date has a timestamp? And that was the reason. I changed the format of the booking date and checked.

Created a new date column(Without time) and used that is joined. Working now. Please find the find attached after signature

example in case file does not open . One of the two ways, a new column

Date = [booking Date].date
or
Date = date(year([booking Date]),month([booking Date]),day([booking Date]))

refer my video, where I covered why TI fails: https://www.youtube.com/watch?v=OBf0rjpp5Hw

[mohsenask]

@amitchandak Ive attached the file here for your review. Can you check and advise?https://www.dropbox.com/s/fu0sn0n2h77xzk1/BearGrylls%20KPI_test.pbix?dl=0 

[amitchandak · Accepted solution]

@mohsenask , I forget to ask. Does the date has a timestamp? And that was the reason. I changed the format of the booking date and checked.

Created a new date column(Without time) and used that is joined. Working now. Please find the find attached after signature

example in case file does not open . One of the two ways, a new column

Date = [booking Date].date
or
Date = date(year([booking Date]),month([booking Date]),day([booking Date]))

refer my video, where I covered why TI fails: https://www.youtube.com/watch?v=OBf0rjpp5Hw

[mohsenask · Accepted solution]

@amitchandak - thanks i got the file and the solution works.

however i had another measure that is applied to the visual for calculating MoM bookings count. 

Bkngs_PrevMnth = CALCULATE(DISTINCTCOUNT('vw_SalesAnalysis_Svcs'[Bookings]),PREVIOUSMONTH(vw_SalesAnalysis_Svcs[Bk Date].[Date])) gives me the count for previous month of Nov 2020 only. No other months show the previous month data

 

the below formula gives me no prev month count at all

Bkngs_PrevMnth = CALCULATE(DISTINCTCOUNT('vw_SalesAnalysis_Svcs'[Bookings]),DATEADD(vw_SalesAnalysis_Svcs[Bk Date],-1,MONTH))

 

[mohsenask]

thanks - it worked after I deleted and recreated the pageg from scratch

Thank you very much @amitchandak 

[mohsenask]

@amitchandak - I need two date tables because they are both different visuals on different pages in the same report

[amitchandak]

@mohsenask , This display means you are displaying the wrong date table. Or the table is missing joins(all value are the same)  and also dates are missing in date table, because values are null (less date in date table)

[amitchandak]

@mohsenask , check if you can work with one calendar using userelationship

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...