Forum Discussion
Previous month calculation
Hi,
I'm looking to add a measure to a filter on to my table.
I need to get the data to display the previous month and up to 10th of the next.
E.g. if my report runs on the 1st April I need the whole of february and upto the 10th of March. Is this possible?
Thanks in advance
Liam
16 Replies
Anonymous
Try Like
last Month = var _max1 = maxx('Sales','Sales'[Date]) var _max = date(year(_max1),month(_max1)-1,10) var _min = date(year(_max),month(_max)-1,1) return CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))Hi amitchandak ,
Would that measure be able to filter the below table?
With the example below it would only have february and the first 10 days of March?
thanks
Liam
Anonymous
Try Like
last Month = var _maxP = maxx('Period','Period'[snapshot Period]) var _max1 = maxx(filter('Period','Period'[snapshot Period]=_maxP),'Period'[Date]) var _max = date(year(_max1),month(_max1)-1,10) var _min = date(year(_max),month(_max)-1,1) return CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))Can you share sample data and sample output.
Hi Liam,
Will you only be selecting single dates?
If not, how is the measure supposed to handle ranges?
Say you select 12th of Feb to 21 of March?
Br,
JHi tex628 ,
Thanks for your reply.
My data comes in in monthly snapshots which I filter on the slicer so E.G. 202002, 202003,202004 etc.
For this specific view I need the data's date to be a month and 10 days in arrreas.
I was thinking of using the slicer to select all and then having a running measure filter to say a month and 10 days previous?
I hope that makes sense,
Thanks
Liam
Alright,
So you currently filter by selecting snapshots in a slicer.
If you select '202002' in you slicer, exactly what range of dates do you want this measure to calculate with?
Br,
J
Hi Anonymous
try like
Measure = var _startDate = DATEADD(SELECTEDVALUE(Table[Date]), -2, MONTH) var _startPeriod = STARTOFMONTH(_startDate) var _endDate = DATEADD(SELECTEDVALUE(Table[Date]), -1, MONTH) var _endPeriod = STARTOFMONTH(_endDate) + 9 RETURN CALCULATE(SUM(Table[Value]), DATESBETWEEN(Table[Date], _startPeriod, _endPeriod) )Hi az38 ,
Its currently coming up with paramter is not the correct type for my dates on row 1 and 3, even though theyre both formatted as dates?
It says selected value has been used in a true/false expression.
Regarding the Return part at the end of the query, this measure is ideally going to filter out rows of a table visual if its in this rolling date range. Would it still have a sum value included?
Thanks so much for your help with this.
Liam
Anonymous
Measure = var _startDate = DATEADD(MAX(Table[Date]), -2, MONTH) var _startPeriod = STARTOFMONTH(_startDate) var _endDate = DATEADD(MAX(Table[Date]), -1, MONTH) var _endPeriod = STARTOFMONTH(_endDate) + 9 RETURN CALCULATE(SUM(Table[Value]), DATESBETWEEN(Table[Date], _startPeriod, _endPeriod) )
