DATESINPERIOD measure not returning the correct total when trying to get the current month sales in a month table

0
votes

I am trying to show the current months sales in a table that also looks at Median, average. The figure returned for each month is correct. However the total is not correct.

SalesMostRecentdaMonth = CALCULATE(Sum('Sales'[Sales]),DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-1,MONTH))

enter image description here

1
powerbi
What do you need to display? what's the desired result? for how the formula is made the total row is correct (last month of sales from the max date, not filtered by month). do you just need the last 12 months of sales or there is something more to consider? – Giovanni Luisotto
Thanks for your questions. I want to display the most recent sales figure for each month and for those sales to show a total for the respective 12 months. There are other columns in the table showing Best, Median, Average. Does that clarify what I am looking for? – allin1

1 Answers

0
votes

So I figured out the answer it was to create a measure that calculated sales in the previous 12 months (trailing 12 months). The video linked below helped. https://www.youtube.com/watch?v=duMSovyosXE

Last 12 Months = 
VAR CurrentDate = MAX(Dates[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate)+1)
VAR Results = 
CALCULATE(SUM('Sales'[Sales]),
FILTER(Dates,Dates[Date] >= PreviousDate&&Dates[Date]<=CurrentDate))
Return 
Results