Forum Discussion

mohsenask's avatar
mohsenask
Helper II
5 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    mohsenask
    5 years ago

    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))
     

     

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    mohsenask
    Helper II
    5 years ago

    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's avatar
      mohsenask
      Helper II
      5 years ago

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

       

      Thank you very much amitchandak