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.