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

[davidknudsen]

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

[davidknudsen]

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]

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

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.

 

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

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

Best Regards,
Angelia

[blopez11 · Accepted solution]

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