Forum Discussion

imranamikhan's avatar
imranamikhan
Icon for Helper V rankHelper V
5 years ago
Solved

Cumulative Measures Two Date Columns One Date Table

Hi everyone,

 

Table A has a column with Customer IDs and a Date column (Record Timestamp). This table is linked to a Date table.

 

Table B has column of Customer IDs and a Date column (Modified Date). This table is linked to Table A (one to many relationship). I then use RELATED to bring the Modified Date as a Column into Table A.

 

As a result Table A has two date columns. Record Timestamp & Modified Date.

 

 

I have two cumulative count measures which I have added as values to a table visual with a Date column from a Date table.

 

Cumulative New Exceptions = 
CALCULATE (
    SUM ( DB_CustomerMasterData_Current_Report[IsNewExceptionCounter] ),
    FILTER (
        ALLSELECTED ( DB_CustomerMasterData_Current_Report ),
        DB_CustomerMasterData_Current_Report[Record Timestamp] <= MAX ( DB_CustomerMasterData_Current_Report[Record Timestamp] )
    )
)

 

Cumulative Closed = 
CALCULATE(distinctcount(DB_CustomerMasterData_Current_Report[MDStatus]),
filter(allselected(DB_CustomerMasterData_Current_Report),
DB_CustomerMasterData_Current_Report[Market Feedback Modified Date] <=max(DB_CustomerMasterData_Current_Report[Market Feedback Modified Date]) && DB_CustomerMasterData_Current_Report[Market Feedback Cleanse Status]= "11. Closed"))

 

The first measure performs a cumulative count of Customer IDs by Record Timestamp. The second measure performs a cumulative count of Customer IDs by a condition and the Modified Date column.

 

The first measure is producing the correct results but the second is repeating the same result across each month. I understand this is because I cannot have multiple paths between 2 date columns to 1 date table.

 

 

Is there a way around this? I understand I could use USERELATIONSHIP or create a separate Date table.

 

Best regards,

Ami

Need Help
  • imranamikhan's avatar
    imranamikhan
    5 years ago

    Thanks all. I just needed an example of applying the syntax - which I have now found.

    Cumulative Closed = CALCULATE (
DISTINCTCOUNT(DB_CustomerMasterData_Current_Report[MDStatus]),USERELATIONSHIP(DB_CustomerMasterData_Current_Report[Market Feedback Modified Date], 'CalendarAuto'[Date]),
FILTER (ALL('CalendarAuto'[Date]),
('CalendarAuto'[Date]) <= MAX ( 'CalendarAuto'[Date] )),DB_CustomerMasterData_Current_Report[Market Feedback Cleanse Status]="11. Closed")

     

5 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi.  Create a second relationship between the date table and the second date field.

     

    In each of your measures you'll want to include this as part of the CALCULATE function: USERELATIONSHIP(DateTable[Date], SecondTable[AppropriateDateField])

  • imranamikhan's avatar
    imranamikhan
    Icon for Helper V rankHelper V
    5 years ago

    Thanks littlemojopuppy . It is not clear from the screenshot but there is already an inactive relationship between the second table and the date table.

     

    Could you provide more detail on how I would modify my measure to include USERELATIONSHIP?

    • littlemojopuppy's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Before you close the parentheses on the CALCULATE statement, insert a comma and then this USERELATIONSHIP(DateTable[Date], SecondTable[AppropriateDateField])

      Of course modify to make sure table and field names are correct

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI imranamikhan,

    It sounds like a common multiple date range analysis requirement, you can take a look at the following link 'start date', 'end date' part of it suitable for your requirement.

    Before You Post, Read This 

    Regards,

    Xiaoxin Sheng

  • imranamikhan's avatar
    imranamikhan
    Icon for Helper V rankHelper V
    5 years ago

    Thanks all. I just needed an example of applying the syntax - which I have now found.

    Cumulative Closed = CALCULATE (
DISTINCTCOUNT(DB_CustomerMasterData_Current_Report[MDStatus]),USERELATIONSHIP(DB_CustomerMasterData_Current_Report[Market Feedback Modified Date], 'CalendarAuto'[Date]),
FILTER (ALL('CalendarAuto'[Date]),
('CalendarAuto'[Date]) <= MAX ( 'CalendarAuto'[Date] )),DB_CustomerMasterData_Current_Report[Market Feedback Cleanse Status]="11. Closed")