Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table looks like below:
I need to create a graph that will show the cumulative value of sales in each month of the year - X axis (... 12.2018, 01.2019, 02.2019, 03.2019), Y axis (number of products sold plus value from all previous months). The table shows the number of products, no date means that the product has not been sold yet.
In addition, I have a slicer on the dashboard that filters based on the product group. So after selecting a specific product group, the chart should only show sales for that group.
I am pretty new in Power BI. I am asking for help because I have been struggling with it for some time and all solutions that I found did not bring the desired result.
Solved! Go to Solution.
Hi @pawelk3 -
Start by creating a Date table
DateTab = ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR([Date]), "Month", MONTH([Date]))
Make a relationship between the date table and your data
Create a new measure Cumulative Sales
Cumulative Sales = TOTALYTD(COUNT(Products[ContractDate]), DateTab[Date])
This will automatically recalculate the total if a Group is selected
All:
Group G1
Hope this helps
David
Oops Sorry! 🙁 Put a parenthesis in the wrong place (remove the one directly after the first instance of DateTab[Date] on the last row, move it to the end of that row).
Cumulative Sales =
CALCULATE (
COUNT ( Products[ContractDate] ),
FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)
Hi @pawelk3 -
Start by creating a Date table
DateTab = ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR([Date]), "Month", MONTH([Date]))
Make a relationship between the date table and your data
Create a new measure Cumulative Sales
Cumulative Sales = TOTALYTD(COUNT(Products[ContractDate]), DateTab[Date])
This will automatically recalculate the total if a Group is selected
All:
Group G1
Hope this helps
David
@dedelman_clng, thank you! It is working great.
I have one more question to the case. What if I want to show the history of all sales on the chart, not only from the current year (also previous years)? How should I modify the 'Cumulative sales' measure?
@pawelk3 - if you just want to add the rest of the dates to the X axis, the calculation takes care of itself. It zeroes out at each January and then grows throughout the year.
Or if you want to compare each month year-over-year, you can use Year as the Legend:
In any case, the measure is fully sliceable across groups.
Hope this helps,
David
@dedelman_clng, what if I want to accumulate sales values for a given group of products from start to finish (showing month by month) until they are sold out? I don't want to reset the values at the beginning of every year.
Thank you for your patience.
@pawelk3 - try this
Cumulative Sales =
CALCULATE (
COUNT ( Products[ContractDate] ),
FILTER ( ALL ( DateTab ), DateTab[Date] ) <= MAX ( DateTab[Date] )
)
This should be able to look at the entire history and add it up. It should be fully sliceable on Group (and on Product).
Hope this helps
David
Oops Sorry! 🙁 Put a parenthesis in the wrong place (remove the one directly after the first instance of DateTab[Date] on the last row, move it to the end of that row).
Cumulative Sales =
CALCULATE (
COUNT ( Products[ContractDate] ),
FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)
HI,
I have a same type of question, but in my case I want to calculate the accumulated values only for a given year in a date tabell, selected from a slicer. How do I limit the calculation to only a selected year?
Best regards
Tove
Hi @Fia123 if you look back up in the thread, the first marked solution has the details and the formula that "resets" every year.
@dedelman_clng, your solution helped me a lot, thank you very much! One additional question arose during the work on the report. How can I stop repeating values for future months within the measure?
With the current solution, the chart shows values for 09.2020, 10.2020, 11.2020 etc. They are the same as the current value for 08.2020 because the table contains only actual sales.
I know that I could limit it only to the current month on the X axis. However, I want to add also the forecasted sales to the chart and then the limit on the X axis will cut my forecast in August.
I would be grateful for an additional hint.
Cumulative Sales No Future =
var __mySales = CALCULATE (
COUNT ( Products[ContractDate] ),
FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
) //Same formula, just want to calculate it once
var __myDate = EOMONTH(MAX(DateTab[Date]), -1) //End of the previous month
RETURN
if (__myDate >= TODAY(), 0, //zero out future months
__mySales)
It should be noted if you intend on putting another measure in the same chart for the future forecast, you probably want to do it as a stacked chart instead of clustered, or there will be unusual looking gaps in the chart.
No good
Better:
Hi @pawelk3 ,
You may create another measure to get previous data like DAX below, and put it into chart visual.
YTD for PY =
CALCULATE (
COUNT ( Products[ContractDate] ),
SAMEPERIODLASTYEAR ( DateTab[Date] )
)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-xicai, thank you Amy. I didn't explain it well enough. I would like to show all months on the x-axis in mm.yyyy format or something similar. I am not talking about accumulating sales within 12 months on the chart, but about accumulating sales in months for different years. Below some example:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |