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 namestart dateend 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?

 

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. 

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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")

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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. 

  • Grambi's avatar
    Grambi
    Frequent Visitor
    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. 

     

    • Grambi's avatar
      Grambi
      Frequent Visitor
      6 years ago

      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.

       

       

      • 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.