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

[Andries_Bosma]

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?

[v-haibl-msft · Accepted solution]

@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 () )
            )
        )

Best Regards,

Herbert

[v-haibl-msft]

@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 ()
                )
            )
        )

Best Regards,

Herbert

[Andries_Bosma]

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

[v-haibl-msft]

@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

[Andries_Bosma]

@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

[v-haibl-msft · Accepted solution]

@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 () )
            )
        )

Best Regards,

Herbert

[gregwilliams55]

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

[Andries_Bosma · Accepted solution]

Marvelous. It works. That simple.

@Herbert, thank you very much.

[hds]

@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!