Previous month calculation

[Anonymous]

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

[amitchandak]

@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))

[Anonymous]

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

[amitchandak]

@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.

[Anonymous]

Hi @amitchandak ,

Whats the best way to send/share the PBIX?

Thanks

Liam

[v-xuding-msft]

Hi @Anonymous ,

You could upload your dummy pbix file into OneDrive for Business and post the link here. Please do mask sensitive data before uploading.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

If you still have questions, please share a sample to us. Then we will understand clearly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @amitchandak ,

I basically want it to show february and up to  march 10 when you select  2020-03 in the snapshot period.

How can I attach the sample file I have?

Thanks in advance

Liam

[az38]

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) )

[Anonymous]

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 

[az38]

@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) )

[Anonymous]

Hi @az38 ,

Do you know what the issue would be if it said MAX has been used in a True/False expression,that is not allowed on the two dateadd statements? 

Thanks

Liam

[Anonymous]

Hi @az38 ,

Thanks again. Im currently still getting the message.

Any ideas?

Thanks

Liam

[tex628]

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,
J

[Anonymous]

Hi @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

[tex628]

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

[Anonymous]

HI @tex628,

I would basically  want 202002 and then the first 10 days from 202003, but rolling.

Thanks

Liam