cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
holydragon Frequent Visitor
Frequent Visitor

Listing tasks with a start / end date in a given month by selecting a month

Hello,

 

I've been pulling my hair out on this one for a few days now.

 

I have a table of tasks that contain a start and end date

 

Task1 Start1 End1

Task2 Start2 End2

etc..

 

I would like the user to use a dropdown window or equivalent to select a month and only display tasks active during that month.

 

January 2018

February 2018

etc..

 

Any suggestions would be greatly appreciated!

 

HD

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Listing tasks with a start / end date in a given month by selecting a month

Hey,

 

assuming that the calendar table is called Calendar and the column that contains the dates is called date, you can get the MIN date or MAX date by using this DAX code, in the example below I create a variable mindate and a variable maxdate ...

 

var mindate = MINX(VALUES('Calendar'[date]), 'Calendar'[date])
var maxdate = MAXX(VALUES('Calendar'[date]), 'Calendar'[date])

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
6 REPLIES 6
Super User
Super User

Re: Listing tasks with a start / end date in a given month by selecting a month

Hey,

 

you should consider to create a pbix file that contains sample data, and upload the file to onedrive or dropbox and share the link.

 

Basically

1. Create a new but unrelated calendar table

2. Create a measure that flags a task with 1, if the selected month is >= the start month and <= the end month of the task.

3. Use the measure to filter the tasks

 

Please consider that a month has a beginning date and an ending date, maybe this has to be considered in the measure I outlined in 2 to fully meet your business requirements.

 

Regards,

Tom

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
holydragon Frequent Visitor
Frequent Visitor

Re: Listing tasks with a start / end date in a given month by selecting a month

Thanks. I will try your suggestion. One additional question. How do select a single month and get the min and max dates?

 

regards,

HD

Super User
Super User

Re: Listing tasks with a start / end date in a given month by selecting a month

Hey,

 

assuming that the calendar table is called Calendar and the column that contains the dates is called date, you can get the MIN date or MAX date by using this DAX code, in the example below I create a variable mindate and a variable maxdate ...

 

var mindate = MINX(VALUES('Calendar'[date]), 'Calendar'[date])
var maxdate = MAXX(VALUES('Calendar'[date]), 'Calendar'[date])

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Super User
Super User

Re: Listing tasks with a start / end date in a given month by selecting a month

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

holydragon Frequent Visitor
Frequent Visitor

Re: Listing tasks with a start / end date in a given month by selecting a month

Hello,

 

thanks for the great suggestions. I'm still trying to work through this and part of the issue could be my newness to DAX.  I'm still encountering issues and have included a screenshot instead of a file for feedback since I can't access G-Drive at the moment due to firewall..

 

I have tasks that only have a start and end date.

I created a lookup calendar with a start and end date for each month.

I created measures that reflect the monthly start and end dates.

I then created the below flag just to test when the start date of the tasks are lower than the start date of the month selected.

 

Flag = IF('Booking'[StartDate] <= [ MinStartData],1,blank)

 

Although the flag is supposed to be either one or blank I received some strange results.

First, I have the same number of flags regardless of what month is selected.

Next, I receive totals.. I presume it may be due to repeating tasks.

 

All that aside, nothing is being filtered based on the month I selected.

 

So far things aren't going according to plan.  Any help is appreciated.

StartEnd_Example.JPG

holydragon Frequent Visitor
Frequent Visitor

Re: Listing tasks with a start / end date in a given month by selecting a month

I finally got something to work.

 

I created two measures.  One for the start and one for the end.  I then just filtered for the flag.

 

Count of StartDate =
var mindate = MINX(VALUES('Calendar'[Start Date]), 'Calendar'[Start Date])
RETURN
CALCULATE(
 COUNTA('Booking'[StartDate]),
 'Booking'[StartDate] < mindate
)

 

I'm not sure that this was the most effective way to go about it but the result is basically what I was looking for.

 

Best,
HD

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 148 members 1,577 guests
Please welcome our newest community members: