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
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
- Brysonds6 years agoHelper III
Exaclty what was needed - thank you so much!