cancel
Showing results for
Did you mean:
Helper I

## Count dates for the month and accumulate values

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.

2 ACCEPTED SOLUTIONS
Super User II

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

Proud to be a Super User!

Super User II

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] ) )
)``````

Proud to be a Super User!

14 REPLIES 14
Super User II

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

Proud to be a Super User!

Helper I

@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?

Super User II

@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

Proud to be a Super User!

Helper I

@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.

Super User II

@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

Proud to be a Super User!

Helper I

@dedelman_clng , unfortunately I am getting an error:

Super User II

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] ) )
)``````

Proud to be a Super User!

Helper II

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

Super User II

Hi @Fia123 if you look back up in the thread, the first marked solution has the details and the formula that "resets" every year.

Proud to be a Super User!

Helper I

@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.

Super User II

``````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)``````

Proud to be a Super User!

Super User II

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:

Proud to be a Super User!

Community Support

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.

Helper I

@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:

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!