Forum Discussion

Andries_Bosma's avatar
Andries_Bosma
Regular Visitor
9 years ago
Solved

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?

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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

  • Andries_Bosma's avatar
    Andries_Bosma
    9 years ago

    Marvelous. It works. That simple.

     

    @Herbert, thank you very much.

8 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      Andries_Bosma
      Regular Visitor
      9 years ago

      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's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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