cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DuzyPat
Frequent Visitor

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Grambi
Frequent Visitor

Re: Showing Quarters in reports

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. 

 

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Re: Showing Quarters in reports

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

 

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


DuzyPat
Frequent Visitor

Re: Showing Quarters in reports

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. 

Super User IV
Super User IV

Re: Showing Quarters in reports

What Exactly you plan to display





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Grambi
Frequent Visitor

Re: Showing Quarters in reports

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. 

 

View solution in original post

DuzyPat
Frequent Visitor

Re: Showing Quarters in reports

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.

Grambi
Frequent Visitor

Re: Showing Quarters in reports

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.

 

Schermafbeelding 2019-10-09 om 16.16.20.png 

DuzyPat
Frequent Visitor

Re: Showing Quarters in reports

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
Frequent Visitor

Re: Showing Quarters in reports

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors