Get sum of sales for last day of each month

[Anonymous]

Need help , @Greg_Deckler @Mich_J @amitchandak 

i need to derive sum of sales carried over against each product for last day of each month . To clairfy i just need sales for last day(only 1 day not the whole month) of each month for last 12 months .

on any given day i might have many products sold  

e.g. in this ex i have 2 products and the amount sold , i need to show a sum of product for last day of each last 12 months . i do have calender table , with months , eom , isweekday

Date	Product	Amount
28 May 2020	A	100
28 May 2020	B	200
29 May 2020	A	200
29 May 2020	B	300
29 Jun 2020	A	300
29 Jun 2020	B	400
30 Jun 2020	A	4000
30 Jun 2020	B	500

[amitchandak]

@Anonymous , use closingbalancemonth(this month end)

openingbalancemonth (last month end)

Discussed in details - https://www.youtube.com/watch?v=yPQ9UV37LOU

Opening -https://www.youtube.com/watch?v=6lzYOXI5wfo

[Anonymous · Accepted solution]

@amitchandak  Thanks , video was helpful  . However i noticed that this only 

CLOSINGBALANCEMONTH only gets data if the sales table has data for month end . what if my last day of sales was 28th Aug 2020 instead of 31st Aug 2020 , i don't see any results for the month of Aug . Keen to hear your thoughts 

[CNENFRNL]

Hi, @Anonymous , I provide you with a solution with more flexibilities. You might want to refer to the attached pbix file for details.

[amitchandak]

@Anonymous , I have used lastnonblankvalue too in the video that can be used

you can also refer : https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Anonymous]

Thanks @amitchandak  i do get the correct data now . Much appreciated .

i do have a follow-up question - if i want to compare this months end sales v/s 6 months ago , i use the below formula but this dosen't seem to be working 

last 6 months ago = calculate (Sum(total sales),DateAdd(date,-6,MONTH))

Any ideas on how to get this comparions Today - last 6 months ago