Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
What I have is a list of projects witih tables
Project name | start date | end date |
So what I would like have is the possibility to choose a quarter (ex. Q2 2019) and it would show all the data for this exact period.
Could you help out with this please?
Solved! Go to Solution.
1. I would create a date table:
DIM_DATE = CALENDAR(FIRSTDATE('start date');LASTDATE('end date'))
2. Add a column to that table for quarter:
Quarter = format(DIM_DATE[Date];"YYYY")&"-Q"&format(DIM_DATE[Date];"Q")
3. Create a slicer based on the quarter field. Select for example 2019-Q2, so you can test the output.
4. Now add the following measure to your original projects table
Project_active_in_selected_quarter = var _firstdate = STARTOFQUARTER(DIM_DATE[Date]) var _lastdate = ENDOFQUARTER(DIM_DATE[Date]) return if(or(FIRSTDATE(Projects['start date'])>_lastdate;FIRSTDATE(Projects['end date'])<_firstdate);"inactive";"active")
This measure finds the start and end date of the selected quarter from your slicer. It then checks whether the project ends before the start of the quarter, or if the project starts after the end of the quarter. If so, it's "inactive". If not, it returns "active".
It's not really designed to support for selection of multiple quarters. If the user selects multiple quarters, for example Q2 and Q4-2019, the measure will check if the project is active in the time between April 1 2019 and December 31 2019. So projects that are only active in Q3 will also be labeled as active.
5. Add this measure to a table containing the projects and check the output.
Hi it's me again . I've clicked resolved too early and it's not quite what I intended. What I would like to achieve is something that is more like events-in-progress - https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/ just like here. Ideally I would like to have a slicer that shows me all the data I need in given period.
I tried adding the measure 'MyOpenOrders_TimePeriod' from that page and it works fine.
Add a Slicer on the field 'Dim_Date[Date]' and add the measure to the table.
The table with project data will now be filtered on projects that are active on at least one of the sliced dates.
MyOpenOrders_TimePeriod = CALCULATE ( DISTINCTCOUNT ( Projects[Project] ); GENERATE ( VALUES ( DIM_DATE[Date] ); FILTER ( Projects; CONTAINS ( DATESBETWEEN ( DIM_DATE[Date]; Projects[StartDate]; Projects[EndDate] ); DIM_DATE[Date];DIM_DATE[Date] ) ) ) )
You'll get something like this.
Hi !
Thanks again it seems to be working , the real issue I have right now is that event If I chose the period it still shows every project.
I mean I've added this to a table and it does show a count of 1 if a project is in that time period however I am wondering is there a way to filter this out so it only shows the projects with the active date.
You could add the measure to the visual filter, set the condition to be higher than 0. This way you don't see the measure value, but the table will only show the active projects based on that measure.
To make sure the end user understands the dynamic between the slicer and the table, you could change the title of the table to:
Measure_Table_Title = "All projects active between "&format(firstdate(DIM_DATE[Date]);"D MMMM YYYY")&" and "&format(LASTDATE(DIM_DATE[Date]);"D MMMM YYYY")
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-format-visual-titles
1. I would create a date table:
DIM_DATE = CALENDAR(FIRSTDATE('start date');LASTDATE('end date'))
2. Add a column to that table for quarter:
Quarter = format(DIM_DATE[Date];"YYYY")&"-Q"&format(DIM_DATE[Date];"Q")
3. Create a slicer based on the quarter field. Select for example 2019-Q2, so you can test the output.
4. Now add the following measure to your original projects table
Project_active_in_selected_quarter = var _firstdate = STARTOFQUARTER(DIM_DATE[Date]) var _lastdate = ENDOFQUARTER(DIM_DATE[Date]) return if(or(FIRSTDATE(Projects['start date'])>_lastdate;FIRSTDATE(Projects['end date'])<_firstdate);"inactive";"active")
This measure finds the start and end date of the selected quarter from your slicer. It then checks whether the project ends before the start of the quarter, or if the project starts after the end of the quarter. If so, it's "inactive". If not, it returns "active".
It's not really designed to support for selection of multiple quarters. If the user selects multiple quarters, for example Q2 and Q4-2019, the measure will check if the project is active in the time between April 1 2019 and December 31 2019. So projects that are only active in Q3 will also be labeled as active.
5. Add this measure to a table containing the projects and check the output.
In your calendar, you can add Qtr by using any of these
Column = Table1[Date Received].[Quarter] &" " & Table1[Date Received].[Year] Column 2 = "Q"&format('Compare Date'[Compare Date],"Q-yyyy")
Hi ,
Thanks for this !
However this resolves the issue partially. Because for example the Project Start is 01.01.2019 and Project End is 10.10.2019 so the table that you've shown can be defined only for the project start OR project end. And when for example I click Q2 it will no show any of those since it starts in Q1 and ends in Q4.
What Exactly you plan to display
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |