Forum Discussion

vtayle900's avatar
vtayle900
Regular Visitor
9 years ago
Solved

Cumulative Sums on different dates

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 co...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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