Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Automatically display next due date for a meeting tasks

Greetings all,

 

I am trying to create a dashboard on Meeting Management, each meeting happens monthly that has several tasks need to happen prior to the meeting day (e.g. Agenda submission, presentation slide submission, minutes submission, etc). Following is the simplified table.

 

Meeting ReferenceMeeting DateAgenda DueMaterials DueMaterial ClearanceMinutes DueFinal Minutes Clearance
Meeting #0120 Jan 20212 Jan 202116 Jan 202118 Jan 202122 Jan 202126 Jan 2021
Meeting #0222 Feb 20213 Feb 202118 Feb 202120 Feb 202124 Feb 20211 Mar 2021

The table is for whole of years (minimum 12 meetings)

 

I was able to get Meeting Reference + Meeting Date for the next meeting (based on Today's date). Also able to generate table for that particular meeting reference with all the related colums e.g. Meeting #01 with all the 6 related columns.

 

>Next I would like to display the next Tasks due i.e. Agenda Due / Materials Due / Material Clearance / Minutes Due / Final Minutes Clearance based on Current Date i.e. if Today = 1 Jan 2021, then need to display "Agenda Due - 2 Jan 2021"; or if Today=17 Jan 2021, then need to display "Material Clearance - 18 Jan 2021". < This is where I am stuck.

 

Highly appreciate your valuabe input, please advise if there are other way of looking at this also.

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    Try the following formula:

     

    Measure = 
var Next_Date = 
    MINX(
        FILTER(
            ALL(MeetingDates_UP),
            MeetingDates_UP[Value] >= TODAY()
        ),
        MeetingDates_UP[Value]
    )
return 
    CALCULATE(
        VALUES(MeetingDates_UP[Attribute]),
        FILTER(
            MeetingDates_UP,
            MeetingDates_UP[Value] = Next_Date
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous best would be to unpivot your table and then it is easy to find what is due next. To unpivot, go to PQ, select meeting column, right-click, unpivot other columns, it will give you two columns, attribute, and date, now you can easily work your logic to find the due/upcoming meeting tasks.

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi parry2k, thank you for the input. I have unpivot, however I am still not sure how to obtain the next "Due Date". I tried the following DAX measure:

       

      Upcoming Task =
      CALCULATE(VALUES(MeetingDates_UP[Attribute]),Filter(MeetingDates_UP,MeetingDates_UP[Value].[Date]=TODAY()+2))
       
      By hardcoding the days value, able to obtain the required "Attribute" i.e. the next task due which is in 2 days.
      How would I automate this without the hardcoding, please? I have tried MIN/MAX, it always throws an error.
       
       
      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        Try the following formula:

         

        Measure = 
var Next_Date = 
    MINX(
        FILTER(
            ALL(MeetingDates_UP),
            MeetingDates_UP[Value] >= TODAY()
        ),
        MeetingDates_UP[Value]
    )
return 
    CALCULATE(
        VALUES(MeetingDates_UP[Attribute]),
        FILTER(
            MeetingDates_UP,
            MeetingDates_UP[Value] = Next_Date
        )
    )

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

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