So I've read and read and wasted more hours than I care to admit trying to solve this problem. I am trying to show current MTD units of sales MTD for this month, which I have solved.
|Cumulative Sale Total MTD = CALCULATE(COUNTA('Sales Occuring'[SaleId]), FILTER(ALL('Sales Occuring'), 'Sales Occuring'[Sale_Date__c] <= MAX('Sales Occuring'[Sale_Date__c]) && MONTH('Sales Occuring'[Sale_Date__c]) = MONTH([Today])))|
Which gives me what I want to see.
However what I want to show is MTD this month w/ MTD Last Month and 2 Months ago. For some reason I am only able to do this by aggregating the data into the Date Table which doesn't allow me to slice it properly and I know isn't the ideal solution.
Below is the goal I am trying to reach.
Hopefully some people who have been helpful on related posts can help me solve this. I've got a feeling I am missing something very simple and have been too stubburn to figure it out on my own. Thanks in advance to anyone who can help.
Have you tried simply using dateadd?
Last Month = CALCULATE([Cumulative Sale Total MTD, DATEADD(Date[DateKey], -1, Month))
2 Month Prior = CALCULATE([Cumulative Sale Total MTD, DATEADD(Date[DateKey], -2, Month))
A FILTER()-based alternative to @kcantor's suggestion. This depends on a date table that has a monotonically incrementing MonthIndex field and a DayOfMonth field.
// DAX // These leverage a MonthIndex which doesn't wrap from 12 to 1 // moving from December of year 1 to January of year 2, rather // continues incrementing, such that January of year 2 is 13, of year // 3 is 25, and so on. // DayofMonth is simply the result of calling the DAY() function - an // integer between 1 and 31 (inclusive). MTD -1 Month = CALCULATE( [Sales] ,FILTER( ALL( DimDate ) ,DimDate[MonthIndex] = MAX( DimDate[MonthIndex] ) - 1 && DimDate[DayOfMonth] <= MAX( DimDate[DayOfMonth] ) ) ) MTD -2 Month = CALCULATE( [Sales] ,FILTER( ALL( DimDate ) ,DimDate[MonthIndex] = MAX( DimDate[MonthIndex] ) - 2 && DimDate[DayOfMonth] <= MAX( DimDate[DayOfMonth] ) ) )