Forum Discussion
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, mont...
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.
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
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?
