Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
Anonymous
Not applicable

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.

 

Schermafbeelding 2019-10-09 om 16.16.20.png 

Anonymous
Not applicable

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

Grambi
Frequent Visitor

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. 

 

amitchandak
Super User
Super User

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.