Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | ||
Solved! Go to Solution.
@amitchandak Thanks , video was helpful . However i noticed that this only
@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
@amitchandak Thanks , video was helpful . However i noticed that this only
Hi, @Anonymous , I provide you with a solution with more flexibilities. You might want to refer to the attached pbix file for details.
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 still way 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |