Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@v-ljerr-msft @kcantor @Greg_Deckler @greggyb
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))
Proud to be a Super User!
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] ) ) )
This is your chart. That's the measure I posted.
I used the exact pattern I posted. My data stops on today's date.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |