Forum Discussion

haichenhuang's avatar
haichenhuang
Frequent Visitor
3 years ago
Solved

Annual Leave Planner Help

Hi there,


So I have data for everyones leave Start and End date, and there is a Code value assigned to it:

 

I have created two more tables for the team members I am interested in seeing their leave dates, and I have created another table for the dates display:

 

 

Then I have created a matrix to display as a calendar:

 

My two questions are:

1. Why are the months not in chronological order?

2.  How do i make it so that the days that they are on leave, as defined by the start and end dates, are shown in the matrix with the leave code value?

 

Thanks.

 

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    Hi haichenhuang ,

     

    Regarding your questions please see the answers below:

     

    1) When you place a column on a visualization the sorting is based on that column in this case since it's a text you will get alphabethical. For this you need to make the sorting of the month name by the month number (check how to sort here)

     

    2) For the second one create a measure similar to this one:

    Days OFf =
COUNTROWS (
    CALCULATETABLE (
        'Days OFF Table',
        'Days OFF Table'[StartDate] <= MAX ( 'calendar'[Date] )
            && 'Days OFF Table'[Enddate] >= MAX ( 'calendar'[Date] )
    )
)

     

    This will give something similar to this:

     

     

4 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, haichenhuang 

    #1
    Your month name is text type, so it is sorted alphabetically.

    You may need to add a column of 'month number' in PowerQuery and sort 'Month name' by 'Month number'.

    Power BI Natural Sorting by Month Name 



    #2

    I don't quite understand your needs.

    Can you share a sample file and expected output here?

     

    Best Regards,
    Community Support Team _ Eason

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

    Hi haichenhuang ,

     

    Regarding your questions please see the answers below:

     

    1) When you place a column on a visualization the sorting is based on that column in this case since it's a text you will get alphabethical. For this you need to make the sorting of the month name by the month number (check how to sort here)

     

    2) For the second one create a measure similar to this one:

    Days OFf =
COUNTROWS (
    CALCULATETABLE (
        'Days OFF Table',
        'Days OFF Table'[StartDate] <= MAX ( 'calendar'[Date] )
            && 'Days OFF Table'[Enddate] >= MAX ( 'calendar'[Date] )
    )
)

     

    This will give something similar to this:

     

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    This works but only the days of the date periods up to the current date are marked the future dates are not displayed as I can do it.

    Thanks a lot

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      3 years ago

      Hi patycnt ,

       

      What do you mean by current date are marked the futures dates?