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