Cumulative Sums on different dates

[vtayle900]

Hi Guys,

I have a Date Dim table and another table which contains

Date Signed, Date Sold, and a status column with both Accepted and Sold . Both are connected to the Date Dim which has a Year column. The Date Signed is the active relationship

I am trying to cumulate by Year based on the two dates. ie one line Signed by year another Sold by Year. 

So far i have tried the below for Date Signed and the values are correct.

SalesContracct ## = CALCULATE (
DISTINCTCOUNT(New_salescontractSet[New_salescontractId]),USERELATIONSHIP(New_salescontractSet[SignedOn], 'DateDim'[Date]),
FILTER (ALL(DateDim[Date]),
('DateDim'[Date]) <= MAX ( DateDim[Date] ) && DateDim[Date] <= TODAY()))

How do i get the cumulative count if the Sold date is not the active relationship and also inlcude the Status of "Sold"

can anyone please assist?

cheers

[Anonymous · Accepted solution]

Hi @vtayle900,

You can use the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. In your scenario, you are able to use USERELATIONSHIP(New_salescontractSet[SoldOn], 'DateDim'[Date]) formula as a filter, and add another filter which includes “Sold” status in Calculate function.

Please check if the following formula meets your requirement. If not, please share sample data of your tables and post expected result here. Also please help to post detailed error message if you get any errors.

SalesContracctSold ## = CALCULATE (
DISTINCTCOUNT(New_salescontractSet[New_salescontractId]),USERELATIONSHIP(New_salescontractSet[SoldOn], 'DateDim'[Date]),
FILTER (ALL(DateDim[Date]),
('DateDim'[Date]) <= MAX ( DateDim[Date] ) && DateDim[Date] <= TODAY()),New_salescontractSet[Status]="Sold")

Thanks,
Lydia Zhang