cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidknudsen
Regular Visitor

DAX running total for profit and months - Exisiting solutions does not work?

Hello PBI – Community, I hope you can help us with this problem.

 

We have a problem creating a measurement which cumulates profit for each months aka. running total measurement in our SuperStoreEU database. Basically we want to create a line chart where the Y-axis is sales profit and X-axis is months.

We have been troubleshooting with the following DAX measures sadly without any luck (https://community.powerbi.com/t5/Desktop/Cumulative-Line-Formula/m-p/22908😞

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)


and this (https://community.powerbi.com/t5/Desktop/DAX-Running-Total-YTD/m-p/21908😞

 

Cumulative = 
VAR RowDate = Table1[Date]
RETURN
    CALCULATE (
        SUM ( Table1[Recurring] );
        FILTER (
            Table1;
            Table1[Date] <= RowDate
                && YEAR ( Table1[Date] ) = YEAR ( RowDate )


When we applied the following DAX measures, the measure worked, but it did not cumulate the profit for each months, hence making a running total .

In fact we tried to copy the exact same setup with the identical excel sheets from http://www.daxpatterns.com/cumulative-total/ without the same results. So do anyone of you have an alternative or experienced anything similar, which might hold the key to how you make a running total in PBI?

Thanks

Regards, David

 

1 ACCEPTED SOLUTION
blopez11
Memorable Member
Memorable Member

Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

View solution in original post

3 REPLIES 3
davidknudsen
Regular Visitor

Thanks alot for your fast replies @blopez11 and @v-huizhn-msft

 

It turned out that the missing date table caused the problem. As @blopez11 mentioned, I needed to refer to a date table where I instead was working with a date field from the same table. I was only working with one table when the problem occured.

 

v-huizhn-msft
Microsoft
Microsoft

@davidknudsen

I am trying to reproduce your scenario. However, I get correct results without any issue. Could you please share more details for further analysis?

I also use the sample date in given link. Create a date table and create the relationship between them as follows.

1.jpg


I created the Cumulative Quantity and get the below screenshot. It still calculate the sum of each month day by day even when you select the month as slicer.

 
2.jpg

In addition, the calculated column also works fine. See following screenshots:

3.jpg

If you still have any problem, please feel free to ask.

Best Regards,
Angelia

blopez11
Memorable Member
Memorable Member

Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors