Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
holydragon
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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

regards,

HD

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.