cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Andries_Bosma
Regular Visitor

How do I create a report which shows running, new, ending and ongoing project in a given period

I have a table (amongst others) with projects with a starting date and optionally an ending date (if empty, the project is still running).

I would like to create a dashboard where users can provide a timespan (start date, end date). Slicer? Parameters?

 

The report should show the count of already running projects before this period, a count of the projects started and ending in this period and the count of project ongoing after this period.

 

I've tried parameters and calculated columns but so far I've got no results.

 

Probably I'm looking for a solution in the wrong direction. Has somebody found out already how to create such a report?

2 ACCEPTED SOLUTIONS

@Andries_Bosma

 

We should only need to make a few changes to the date filter in the measure formulas. Please try with following measures. You can take a look at the filter parts of them, and adjust if I misunderstand the condition.

 

running = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER ( Table1, Table1[start_date] < MinDate )
        )
starting = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                Table1[start_date] >= MinDate
                    && Table1[start_date] <= MaxDate
            )
        )
ending = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                Table1[end_date] >= MinDate
                    && Table1[end_date] <= MaxDate
            )
        )
ongoing = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                OR ( Table1[end_date] > MaxDate, Table1[end_date] = BLANK () )
            )
        )

How do I create a report which shows running, new, ending and ongoing project in a given period_1.jpg

 

Best Regards,

Herbert

View solution in original post

Marvelous. It works. That simple.

 

@Herbert, thank you very much.

View solution in original post

8 REPLIES 8
v-haibl-msft
Microsoft
Microsoft

@Andries_Bosma

 

You can create another Calendar table but do not create relationship with your project table. Use Calendar[Date] in the Slicer. Create three measures for the desired three results.

 

running projects before this period = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER ( Table1, Table1[StartDate] < MinDate )
        )
projects started and ending in this period = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                Table1[StartDate] >= MinDate
                    && Table1[EndDate] <= MaxDate
                    && Table1[EndDate] <> BLANK ()
            )
        )
project ongoing after this period = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                OR (
                    Table1[StartDate] >= MinDate
						&& Table1[StartDate] <= MaxDate
                        && Table1[EndDate] > MaxDate,
                    Table1[StartDate] >= MinDate
                        && Table1[StartDate] <= MaxDate
                        && Table1[EndDate] = BLANK ()
                )
            )
        )

How do I create a report which shows running, new, ending and ongoing project in a given period_1.jpg

 

Best Regards,

Herbert

@v-haibl-msft

Thank you for you quick response. I implemented your example and it works. Great. But ...

 

I would like to create output with severel departments included:

 

                  running      starting       ending     ongoing

dep 1             13               3                2               14

dep 2               8               0                3                 5

dep 3              27             10               1                36

 

I didn't figure out how to do this.

 

And, by the way, do you know how to alter the date format in the date-slicer?

 

Thanks in advance for your reply.

@Andries_Bosma

 

Could you please give a sample of your table (including departments column)?

You can change the date format by selecting the date column, then choose your preferred format in Modeling ribbon – Formatting – Format. The format will change when you change the date slicer from Between to List.

 

Best Regards,

Herbert

@Herbert, thanks again for the attention you give to my questions.

 

An example of the underlaying database could be:

 

department_id     project_id    start_date      end_date

100                       1023            3/21/2012     10/23/2016

100                        4068           5/22/2014     12/21/2015

100                       1014            5/5/2015        null

107                       2312            5/13/2014      7/24/2016

107                       2322            5/13/2014      null

@Andries_Bosma

 

We should only need to make a few changes to the date filter in the measure formulas. Please try with following measures. You can take a look at the filter parts of them, and adjust if I misunderstand the condition.

 

running = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER ( Table1, Table1[start_date] < MinDate )
        )
starting = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                Table1[start_date] >= MinDate
                    && Table1[start_date] <= MaxDate
            )
        )
ending = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                Table1[end_date] >= MinDate
                    && Table1[end_date] <= MaxDate
            )
        )
ongoing = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    VAR MaxDate =
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                Table1,
                OR ( Table1[end_date] > MaxDate, Table1[end_date] = BLANK () )
            )
        )

How do I create a report which shows running, new, ending and ongoing project in a given period_1.jpg

 

Best Regards,

Herbert

Hello-

 

These measures worked perfect for a very similar report that I am creating. So, thank you.

 

However, is there anyway to use SAMEPERIODLASTYEAR with these measures? I'd like to use these to easily calculate YoY growth for project counts.

 

As an example: LY Running = CALCULATE(Table1[running],SAMEPERIODLASTYEAR('Calendar'[Date])

 

I've tried and the measure is not plotting in visulations using periods (months, quarters, etc) from the 'Calendar' table. 

 

Thanks, Greg

 

PS - Below is an actual example of a measure that I (created using the logic you demonstrated in this thread) would like to apply sameperiodlastyear function to.

 

CreatedAndFinishInPeriod = 
    VAR MinDate =
        CALCULATE ( MIN ( 'Dates'[Date] ), ALLSELECTED ( 'Dates'[Date] ) )
    VAR MAXDate=
		CALCULATE(MAX('Dates'[Date]),ALLSELECTED('Dates'[Date]))
	RETURN
        CALCULATE (
            DISTINCTCOUNT ( 'MAC Project Data ALL'[Project ID] ), 
            FILTER ( 'MAC Project Data ALL',				 
				'MAC Project Data ALL'[Created] >= MinDate && 
				'MAC Project Data ALL'[Created] <= MAXDate && 
				'MAC Project Data ALL'[Actual Finish Date]>=MinDate &&
				'MAC Project Data ALL'[Actual Finish Date]<=MAXDate && 
				'MAC Project Data ALL'[Actual Finish Date]<>Blank()
)
        )

Marvelous. It works. That simple.

 

@Herbert, thank you very much.

@Andries_Bosma,

 

I strongly suggest that You mark @v-haibl-msft's reply as the solution so that I and the others who would refer to this thread would saw the answer rightly.

searching this thread and seeing

"

Marvelous. It works. That simple.

 

@Herbert, thank you very much.

"

Marked as the answer makes no sense!

Thank you!

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.