Forum Discussion
Calculation based on AND of selected filter values
- 6 years ago
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
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.
Nathaniel