Previous month of last selected month if multiple months are selected

[Anonymous]

Hi,
I have column Date and Sales ,I need to show data on last selected month(last date) if multiple months are selected ans same for previous month 
Providing snap of dummy Data and expected result 

if I select Jan-21,Feb-21,Mar-21
I want Current Month Sales as = 100 (data on last date of march)
Previous Month = 150 (data on last date of dec)

Can anyone pls suggest me how to solve this?

Thanks.

[Anonymous]

its not working in my case Thanks for the reply

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check these formulas:

Current Month = 
var max_date = CALCULATE(MAX('Table'[date]),ALLSELECTED('Table'))
return
CALCULATE(SUM('Table'[sales]),FILTER(ALL('Table'),'Table'[date]=max_date))

Previous Month = 
var max_date = CALCULATE(MAX('Table'[date]),ALLSELECTED('Table'))
var count_months = DISTINCTCOUNT('Table'[Month year])
var pre_date = EDATE(max_date,-count_months)
return
CALCULATE(SUM('Table'[sales]),FILTER(ALL('Table'),'Table'[date]=pre_date))

Best Regards,

Jay

[Anonymous]

Hi 

Hi @Anonymous ,
I have one more query ,I am unable to filter above calculated measure with other column ,
how can I resolve this

[Anonymous]

Hi @Anonymous ,

You can add the condition to the filter function.

For example:

CALCULATE(SUM('Table'[sales]),FILTER(ALL('Table'),'Table'[date]=max_date&&'table'[column]=selectedvalue(othercolumn)))

Best Regards,

Jay

[Anonymous]

Am I missing anything in this ? plus I want to filter two different column (i.e.platform and type )values  

[Anonymous]

Hi @Anonymous 
I have resolved filtering part ,using below measure it seems to work

but now I am not geting incorrect value if I am selecting last month as feb
as per my requirement it should give me data on sept 30 but it is giving value for sept 28 please refer blow image 

and It is working fine with other months selection only causing this issue if I am selecting feb as my latest month

[Anonymous]

Thaaaank You sooo much ....exactly the solution I needed!

[Anonymous]

Hi Amit,
Thanks for your Reply ,but its not working in my case I am sharing an Image with Deatiled Explaination 

Firstly I have tried your DAX it is giving me Blank If I select single month.
and If I select Multilple Month as above Image It is till incorrect 
As I have selected (2019-03,2019-04) so what I want is , it should reflect (4198259)
Any help on this ?

[amitchandak]

@Anonymous , Try a month behind formula

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))