Forum Discussion
Calculation based on AND of selected filter values
Hi - I want to create a variance calculation based on the combination of 2 values within a single date slicer. The calculation is different for each combination since the months will be different.
I know what calculation I can use and how to achieve most of this, but I am stuck on how to restrict the calculation to 2 values in the slicer.
So simply put, I am trying to achieve this: IF PERIODS SELECTED = JAN AND FEB, THEN (JAN - FEB)
I've tried several variations of this formula, but I am stuck on the AND part of the clause.
TD Variance =
SWITCH(TRUE(),
VALUES('ABCD Customer YTD'[Period])=DATE(2019,1,1) && VALUES('ABCD Customer YTD'[Period])=DATE(2019,2,1),
CALCULATE(
SUM('ABCD Customer YTD'[(YTD) Sales Amount]),
'ABCD Customer YTD'[Period] IN { DATE(2019, 1, 1) }
) -
CALCULATE(
SUM('ABCD Customer YTD'[(YTD) Sales Amount]),
'ABCD Customer YTD'[Period] IN { DATE(2019, 2, 1) }
),
//Additional combinations will go here//
BLANK())
Hello Brysonds
If I am understanding correctly this is fairly straight forward with a date table that contains a column with the year and month combined into a single number. This DAX code will give us a simple date table to use. Enter it in modeling > new table.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR([Date]), "Month",FORMAT([Date],"mmmm"), "Year Month", FORMAT([Date],"yyyy-mmmm"), "YearMonthSort",YEAR([Date])*100 + MONTH([Date]), "ShortName",FORMAT([Date],"ddd"), "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7} )You would join this into your model by linking Dates[Date] > 'ABCD Customer YTD'[Period].
We will want a measure that just sums the amount.
Sales Amount = SUM('ABCD Customer YTD'[(YTD) Sales Amount])Then this measure will calculate the lowest Year Month and the highest year month and give us the difference.
Difference = VAR FirstMonth = MIN ( 'Dates'[YearMonthSort] ) VAR LastMonth = MAX ( 'Dates'[YearMonthSort] ) VAR FirstMonthAmt = CALCULATE( [Sales Amount], 'Dates'[YearMonthSort] = FirstMonth ) VAR LastMonthAmt = CALCULATE( [Sales Amount], 'Dates'[YearMonthSort] = LastMonth ) RETURN FirstMonthAmt - LastMonthAmt
3 Replies
Brysonds ,
Not sure that I get this, but if MIN(Date) and MAX(Date) or FIRSTDATE(Date) and LASTDATE(Date) when selected by slicer period should work.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielHello Brysonds
If I am understanding correctly this is fairly straight forward with a date table that contains a column with the year and month combined into a single number. This DAX code will give us a simple date table to use. Enter it in modeling > new table.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR([Date]), "Month",FORMAT([Date],"mmmm"), "Year Month", FORMAT([Date],"yyyy-mmmm"), "YearMonthSort",YEAR([Date])*100 + MONTH([Date]), "ShortName",FORMAT([Date],"ddd"), "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7} )You would join this into your model by linking Dates[Date] > 'ABCD Customer YTD'[Period].
We will want a measure that just sums the amount.
Sales Amount = SUM('ABCD Customer YTD'[(YTD) Sales Amount])Then this measure will calculate the lowest Year Month and the highest year month and give us the difference.
Difference = VAR FirstMonth = MIN ( 'Dates'[YearMonthSort] ) VAR LastMonth = MAX ( 'Dates'[YearMonthSort] ) VAR FirstMonthAmt = CALCULATE( [Sales Amount], 'Dates'[YearMonthSort] = FirstMonth ) VAR LastMonthAmt = CALCULATE( [Sales Amount], 'Dates'[YearMonthSort] = LastMonth ) RETURN FirstMonthAmt - LastMonthAmt
Exaclty what was needed - thank you so much!
