cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Get sum of sales for last day of each month

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

DateProductAmount  
28 May 2020A100  
28 May 2020B200  
29 May 2020A200  
29 May 2020B300  
29 Jun 2020A300  
29 Jun 2020B400  
30 Jun 2020A4000  
30 Jun 2020B500  
     
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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 

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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
Not applicable

@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 

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

Screenshot 2020-12-03 203646.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

@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
Not applicable

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 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors