topic Re: Get sum of sales for last day of each month in DAX Commands and Tips

Get sum of sales for last day of each month

Anonymous — Thu, 03 Dec 2020 07:02:31 GMT

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

Re: Get sum of sales for last day of each month

amitchandak — Thu, 03 Dec 2020 07:06:27 GMT

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

Re: Get sum of sales for last day of each month

Anonymous — Thu, 03 Dec 2020 11:12:32 GMT

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

Re: Get sum of sales for last day of each month

amitchandak — Thu, 03 Dec 2020 12:41:56 GMT

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

Re: Get sum of sales for last day of each month

CNENFRNL — Thu, 03 Dec 2020 19:39:40 GMT

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

Re: Get sum of sales for last day of each month

Anonymous — Tue, 08 Dec 2020 09:58:46 GMT

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