Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pawelk3
Helper I
Helper I

Count dates for the month and accumulate values

Hi,

 

I have a table looks like below:

 

pawelk3_2-1597170536183.png

 

 

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.

pawelk3_1-1597170424576.png

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
dedelman_clng
Community Champion
Community Champion

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

 

2020-08-11 14_02_05-N_and_BASE_problem (3) - Power BI Desktop.png

 

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:

2020-08-11 14_04_13-N_and_BASE_problem (3) - Power BI Desktop.png

 

Group G1

 

2020-08-11 14_04_57-N_and_BASE_problem (3) - Power BI Desktop.png

 

Hope this helps

David

View solution in original post

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

 

View solution in original post

14 REPLIES 14
dedelman_clng
Community Champion
Community Champion

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

 

2020-08-11 14_02_05-N_and_BASE_problem (3) - Power BI Desktop.png

 

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:

2020-08-11 14_04_13-N_and_BASE_problem (3) - Power BI Desktop.png

 

Group G1

 

2020-08-11 14_04_57-N_and_BASE_problem (3) - Power BI Desktop.png

 

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.

 

2020-08-12 07_18_27-N_and_BASE_problem (3) - Power BI Desktop.png

2020-08-12 07_17_06-N_and_BASE_problem (3) - Power BI Desktop.png

 

Or if you want to compare each month year-over-year, you can use Year as the Legend:

 

2020-08-12 07_20_34-N_and_BASE_problem (3) - Power BI Desktop.png

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

@dedelman_clng , unfortunately I am getting an error:

pawelk3_0-1597242346011.png

 

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)

 

2020-08-18 18_00_38-N_and_BASE_problem (3) - Power BI Desktop.png

 

 

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

2020-08-18 18_05_20-N_and_BASE_problem (3) - Power BI Desktop.png

 

Better:

2020-08-18 18_05_56-N_and_BASE_problem (3) - Power BI Desktop.png

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:

pawelk3_0-1597234405679.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.