Calculate sum based on another table/columns

[Nabm12]

 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.

       

[Ashish_Mathur]

Hi,

I have answered a similar question here - Split total patient hospitalisation days into multiple months.

Hope this helps.

[OwenAuger]

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]

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]

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