topic Re: Cumulative Formula by date in DAX Commands and Tips

Cumulative Formula by date

AivaS — Mon, 13 Jan 2025 10:59:48 GMT

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.

Re: Cumulative Formula by date

johnt75 — Mon, 13 Jan 2025 11:05:44 GMT

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

Re: Cumulative Formula by date

divyed — Mon, 13 Jan 2025 11:14:22 GMT

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

Re: Cumulative Formula by date

AivaS — Mon, 13 Jan 2025 12:31:09 GMT

Unfortunately, it still has the same result.

Re: Cumulative Formula by date

AivaS — Mon, 13 Jan 2025 12:38:16 GMT

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.

Re: Cumulative Formula by date

Anonymous — Tue, 14 Jan 2025 07:11:10 GMT

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.

Re: Cumulative Formula by date

AivaS — Tue, 14 Jan 2025 07:38:43 GMT

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?

Re: Cumulative Formula by date

AivaS — Tue, 14 Jan 2025 07:57:57 GMT

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.

Re: Cumulative Formula by date

divyed — Tue, 14 Jan 2025 08:03:49 GMT

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.

Re: Cumulative Formula by date

Anonymous — Wed, 15 Jan 2025 09:28:46 GMT

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.