Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |