Forum Discussion

Gauravi's avatar
Gauravi
New Member
4 years ago
Solved

DAX Calculation

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?

Need Help
  • Icey's avatar
    Icey
    4 years ago

    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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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])))

  • Gauravi's avatar
    Gauravi
    New Member
    4 years ago

    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

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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's avatar
      Gauravi
      New Member
      4 years ago

      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?

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    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.