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
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
Resident Rockstar
Resident Rockstar

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
Employee
Employee

@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
Resident Rockstar
Resident Rockstar

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

 

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.