Forum Discussion

Nabm12's avatar
Nabm12
Regular Visitor
8 years ago

Calculate sum based on another table/columns

 Hello all, I seem to be stuck on creating a column for a dataset. I have two tables, one with dates and another with the status of projects, submitted, and closing dates. What I want to do is calculate a sum of how many projects are active during each month. For example, if a project was submitted on 1/1/2018 and closed on 4/1/2018, it would have been active during 2/1/2018 and 3/1/2018. Therefore, in the new calculated column in the Date table, 2/1/2018 would have a value of 1 and 3/1/2018 would have a value of 1. If another project was submitted on 2/1/2018 and had no closing date, then it would be active during 3/1/2018 and so on. This would result in a total value of 2 for 3/1/2018, etc. Currently, I have a one to many relationship (Date to SubmittedDate). In the end, I would like to create a chart with the dates on the x axis and have the number of active projects during each month as the column values.

 

       

 

 

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    Hello Nabm12

     

    Your situation is similar to this recent thread here

     

    You need an 'events in progress' measure- have a read of that thread.

     

    Your options are basically either to

    • Have a disconnected Date table, and write a measure that filters the Projects table appropriately based on the selected Dates
    • Create a restructured version of your Projects table that has a row for every date that a Project is active.

    Regards,

    Owen

    • Nabm12's avatar
      Nabm12
      Regular Visitor
      8 years ago

      Hi OwenAuger

       

      Thanks for the reply! 

      For my data, I used the following DAX query:

       

      Measure = CALCULATE (
    COUNT ( 'PSG'[Status] ),
    GENERATE (
        VALUES ( 'Date'[Date] ),
        FILTER (
            'PSG',
            CONTAINS (
                DATESBETWEEN (
                    'Date'[Date],
                    'PSG'[SubmittedDate],
                    'PSG'[ClosedDate]
                ),
                [Date], 'Date'[Date]
            )
        )
    )
)

       

       

      However, I am not getting the results I wanted. Is there something that I am missing? For example, it is counting the SubmittedDates which are null, resulting in a measure of 2 for months that should be 0.

       

       

       

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        8 years ago

        Hi again Nabm12

         

        Glad to see you implemented one of the 'events in progress' patterns :)

         

        I see the problem - if you have blank SubmittedDates, the project is treated as having always been in progress.

        This is because DATESBETWEEN treats a blank start_date as -∞ and a blank end_date as +∞.

         

        We want this behaviour for blank end_dates in your case, but only if start_date is not blank.

         

        The following measure should fix this behaviour, by only including rows of PSG where PSG[SubmittedDate] is nonblank.

         

         

        Measure = 
CALCULATE (
    COUNT ( PSG[Status] ),
    GENERATE (
        VALUES ( 'Date'[Date] ),
        FILTER (
            PSG,
            IF (
                PSG[SubmittedDate], // This is the same as NOT ( ISBLANK ( PSG[SubmittedDate] ) )
                CONTAINS (
                    DATESBETWEEN (
                        'Date'[Date],
                        PSG[SubmittedDate],
                        PSG[ClosedDate]
                    ),
                    'Date'[Date], 'Date'[Date]
                )
            )
        )
    )
)

         

        I tested at my end with your sample data, and the Measure first appears in September 2017 with value 1, so has eliminated the two unwanted rows.

         

        Regards,

        Owen