Cumulative Formula by date

[AivaS]

Dear All,

I am been trying to workout why the Cumulative cost is not working, please see my forumal below.

RT Profit =

VAR MaxDate = MAX ( 'Calendar'[Date] ) 

RETURN

    CALCULATE (

        [PROFIT M],        

        'Calendar'[Date] <= MaxDate, 

        ALL ('Calendar'[Date])           

    )

The results just matching the Profit by month, not cumulative.

Below is the table and Data view. 

[AivaS]

I thinkI need to add a filter in the formula? it seems then I use the "Calendar" Table, I get this resulin the table ? 
Sum of profit being a column, Profit M being a measure, RT profit is the one I amtrying to work out. 

[v-linyulu-msft · Accepted solution]

Hi, @AivaS 
 Thank you for your prompt response.

1.You can try the following measure:

Measure = 
VAR table1 =
    SUMMARIZE(
        ALLSELECTED('summary_by_month'),
        'summary_by_month'[Date].[Month],
        "AVG",
        VAR cm = 'summary_by_month'[Date].[Month]
        VAR _averageProfit =
            CALCULATE(
                AVERAGE(summary_by_month[profit]),
                FILTER(
                    ALL(summary_by_month),
                    'summary_by_month'[Date].[Month] = cm
                )
            )
        RETURN _averageProfit,
        "INEDX1",
        SWITCH(
            'summary_by_month'[Date].[Month],
            "January", 1,
            "February", 2,
            "March", 3,
            "April", 4,
            "May", 5,
            "June", 6,
            "July", 7,
            "August", 8,
            "September", 9,
            "October", 10,
            "November", 11,
            "December", 12
        )
    )
 
VAR table2 =
    SUMMARIZE(
        table1,
        'summary_by_month'[Date].[Month],
        [INEDX1],
        "running",
        SUMX(
            FILTER(table1, [INEDX1] <= EARLIER([INEDX1])),
            [AVG]
        )
    )
 
VAR f =
    SUMX(
        FILTER(table1, [INEDX1] = MAX([INEDX1])),
        [AVG]
    )
 
VAR f1 =
    SUMX(
        FILTER(table2, 'summary_by_month'[Date].[Month] = MAX('summary_by_month'[Date].[Month])),
        [running]
    )
 
RETURN
    IF(
        ISINSCOPE('summary_by_month'[Date].[Month]),
        f1,
        f
    )

2.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[divyed]

Hello @AivaS ,

Your DAX formula for calculating the cumulative profit has a small issue.

Modify your dax like below and try again :

RT Profit =
VAR MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
[PROFIT M],
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= MaxDate
)
)

I hope this helps.

Cheers

[AivaS]

Unfortunately, it still has the same result. 

[divyed]

Hello @AivaS ,

I have tested on dummy data and it is working. I have created a measure total_Sales and I have da date table. Please replace your tables accordingly and try again

Here is my dax :

RunningTotalSales =

VAR CurrentDate =

    MAX ( Datestbl[Date] )

RETURN

    IF (

        NOT ( ISBLANK ( Measurestbl[TotalSales] ) ),

        CALCULATE ( Measurestbl[TotalSales], Datestbl[Date] <= CurrentDate ),

        BLANK ()

    )

I hope this helps .

Did I answer your query ? Mark this as solution if this solves your issue, kudos are appreciated.

Cheers.

[johnt75]

Your RT Profit is a calculated column. Delete the column and use the same code to make a measure.

[AivaS]

RT profit is a measure, 
maybe the issue is that in RT profit measure I am using Profit M, measure which is created by the following formula :

PROFIT M = AVERAGE('summary_by_month'[profit])

since the "profit" is a column and I needed a measure to calculate. 

[v-linyulu-msft]

Thanks for the reply from divyed and johnt75 , please allow me to provide another insight:

Hi, @AivaS 
Thanks for reaching out to the Microsoft fabric community forum.

The most efficient solution currently is to use visual object calculations:

1.Here is my test data:

Initial visualization results:

2.Click on the visual object and select visual calculation:

3.Here's my final result, which I hope meets your requirements.

4.For further details, please refer to:

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AivaS]

Excellent, thank you so much. That was very easy, indeed, however, I will need to use the measure of Running Total in KPI cards, so from testing out it seems the visual calculation only works for that particular visual, and does not create a measure?