09-23-2016 11:05 AM
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.
09-23-2016 11:25 AM
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))
09-23-2016 01:22 PM
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] ) ) )
09-30-2016 09:38 AM - edited 09-30-2016 09:38 AM
10-05-2016 03:16 PM - edited 10-05-2016 03:17 PM