DAX Calculation

[Gauravi]

The following is my data model:

1. Contracts table with the following fields – ContractId, SalesRepId, CreatedDate, ContractAmount, CancelledDate
2. Date table with basic date fields – date, month, quarter. Contracts table and Date table are joined on CreatedDate
3. SalesPerson – SalesRepId, SalesRepName

I need a DAX calculated to show the total contract amount created last year but cancelled this year. For example, if a user selects 2021, the calculation should show the sum of contract amount created in 2020 but cancelled in 2021. Similarly, if a user selects Jan 2021, the calculation should show the sum of contract amount created in Jan 2020 but cancelled in Jan 2021.

Any idea how to do this?

[Icey · Accepted solution]

Hi @Gauravi ,

Try this:

Measure =
VAR SelectedDate_ =
    VALUES ( 'Date'[Date] )
VAR SamePeriodLastYear_ =
    CALCULATETABLE ( VALUES ( 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        SUM ( Contracts[ContractAmount] ),
        ALL ( 'Date' ),
        Contracts[CreatedDate] IN SamePeriodLastYear_,
        Contracts[CancelledDate] IN SelectedDate_
    )

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18]

Hi @Gauravi ,

You can try any of the below measure:-

Contract_Sales =
VAR selected_year =
    SELECTEDVALUE ( date[year] )
RETURN
    CALCULATE (
        SUM ( Contract[ContractAmount] ),
        CALCULATETABLE (
            VALUES ( Contract[ContractAmount] ),
            YEAR ( Contract[CreatedDate] ) = selected_year - 1
        ),
        CALCULATETABLE (
            VALUES ( Contract[ContractAmount] ),
            YEAR ( Contract[CancelledDate] ) = selected_year
        )
    )

Contract_sales =
VAR selected_year =
    SELECTEDVALUE ( date[year] )
RETURN
    CALCULATE (
        SUM ( Contract[ContractAmount] ),
        FILTER (
            Contract,
            YEAR ( Contract[CreatedDate] ) = selected_year - 1
                && YEAR ( Contract[CancelledDate] ) = selected_year
        )
    )

Thanks,

Samarth

[Gauravi]

The calculation is not working because my contracts table is linked/joined to the date dimension. Is there a way to make it work with the join to the date table?

[Gauravi]

The calculation returns blank. If I just use one condition (contract date or canceled date), it shows the correct value. But if I use both the conditions, it returns blank

[amitchandak]

@Gauravi , Try a measure like

Year behind Sales = CALCULATE(CALCULATE(SUM(Contract[ContractAmount]),dateadd('Date'[Date],-1,Year)), filter(Contract, year(Contract[Cancelled Date]) = year(max('Date'[Date])))