Forum Discussion
Matrix problems with calendar
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
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
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 datathe below formula gives me no prev month count at allBkngs_PrevMnth = CALCULATE(DISTINCTCOUNT('vw_SalesAnalysis_Svcs'[Bookings]),DATEADD(vw_SalesAnalysis_Svcs[Bk Date],-1,MONTH))
7 Replies
mohsenask , check if you can work with one calendar using userelationship
amitchandak - I need two date tables because they are both different visuals on different pages in the same report
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 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
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
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 datathe below formula gives me no prev month count at allBkngs_PrevMnth = CALCULATE(DISTINCTCOUNT('vw_SalesAnalysis_Svcs'[Bookings]),DATEADD(vw_SalesAnalysis_Svcs[Bk Date],-1,MONTH))