Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Showing Quarters in reports

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...
Need Help
Tips and Tricks
Tutorial Requests
  • Grambi's avatar
    Grambi
    6 years ago

    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. 

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

 

Grambi's avatar
Grambi
Frequent Visitor
6 years ago

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