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.
I've been spinning my wheels too long with this one so hopefully someone is albe to assist. I'm trying to create a line graph that displays the running total of sales for the selected month and selected division. Ideally there are 2 slicers on the page, one for month and one for division. The line graph will show the cumultaive total of sales for the selected month and division with the day of the month on the x-axis and sales total as the y-axis. Below is a sample data set I'm working with. The 'Cuml Sales by month' column is what I've manually entered, that is the column I'm having issues getting correct. The result can be either a column or measure, I don't think it matters to me as long as I can plot it on the line graph. Thanks in advance
Division | Date | Month | Day | Sales | Cuml Sales by Month |
New York | 7/16/2023 | 7 | 16 | 2 | 2 |
New York | 7/18/2023 | 7 | 18 | 4 | 6 |
New York | 8/1/2023 | 8 | 1 | 2 | 2 |
New York | 8/5/2023 | 8 | 5 | 5 | 7 |
New York | 8/15/2023 | 8 | 15 | 1 | 8 |
New York | 8/16/2023 | 8 | 16 | 3 | 11 |
New York | 8/21/2023 | 8 | 21 | 2 | 13 |
Washington | 7/20/2023 | 7 | 20 | 2 | 2 |
Washington | 7/25/2023 | 7 | 25 | 1 | 3 |
Washington | 8/2/2023 | 8 | 2 | 5 | 5 |
Washington | 8/4/2023 | 8 | 4 | 3 | 8 |
Washington | 8/10/2023 | 8 | 10 | 1 | 9 |
Washington | 8/13/2023 | 8 | 13 | 4 | 13 |
Washington | 8/15/2023 | 8 | 15 | 2 | 15 |
Atlanta | 7/17/2023 | 7 | 17 | 3 | 3 |
Atlanta | 7/21/2023 | 7 | 21 | 1 | 4 |
Atlanta | 7/25/2023 | 7 | 25 | 2 | 6 |
Atlanta | 8/5/2023 | 8 | 5 | 1 | 1 |
Atlanta | 8/7/2023 | 8 | 7 | 3 | 4 |
Atlanta | 8/9/2023 | 8 | 9 | 2 | 6 |
Atlanta | 8/14/2023 | 8 | 14 | 1 | 7 |
Hi @kkassel
for running total you can try if you have a date table
MTD Total = TOTALMTD=SUM(Sales[SalesByMonth], DimDate[Date])
or if you don't have a date table you can try this article https://community.fabric.microsoft.com/t5/Desktop/MTD-YTD-Calculation-on-Non-Standard-Date-tables/td...
Thanks
Joe
If this post helps, then please Accept it as the solution
Thanks for the quick response. Maybe my description wasn't clear but the 'Cuml Sales by month' column in the sample data is the column I'm trying to calcuate in power bi. In the sample data, I just manually added that so people could see what I'm looking to calculate. The examples you gave seem to be totaling that column but I need a measure or column coding that will calculate that for me.
no worries. I had to add CALCULATE() function to beginning of string for the SUM() function to work but that seems to have done the trick. So simple in the end. I was trying to make it way too complicated. Thanks!
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 |
---|---|
100 | |
99 | |
76 | |
67 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |