Dax to calculate cumulative sum of columns of 2 different tables

[Siddiq1212]

Hi experts,

I am new to the community. Thank you for your support.

I am trying to find a dax to cacluate cumulative sum of these two columns (which are from 2 different tables).

Any guess...

[v-karpurapud]

Hi @Siddiq1212 

Please try the below DAX:

Cum Combined Cost :=

VAR MaxEarned = CALCULATE(

    MAX('TableA'[Cum Earned Value Cost]),

    ALL('TableA')

)

RETURN

IF(

    NOT ISBLANK('TableB'[Cum Forecast Cost]) && 'TableB'[Cum Forecast Cost] > 0,

    'TableB'[Cum Forecast Cost] + MaxEarned,

    BLANK()

)

If this doesn’t fully meet your needs, could you kindly share a sample of your data or .PBIX file? That would help us provide a more accurate solution.
 

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.


Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.
 

[Siddiq1212]

Hi Karpurapu D,

I am unable to upload .pbix file. Kindly Suggest (I am new here.)

[v-karpurapud]

Hi @Siddiq1212 

Sure, I will guide you through the steps below.

Navigate to the forum, and under the reply box, click on the browser.

After that, a folder tab will open. Choose the appropriate PBIX file, click on open, and then upload.

Once the file is successfully loaded, click on post.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[Siddiq1212 · Accepted solution]

I am sorry, it is quit hard for me, am not able to find it.

[Siddiq1212]

Hi @v-karpurapud 

https://1drv.ms/u/c/44f6f494a38cf86f/EVKVnXnvx_hJj7DTAZk3eGIB-1fSacpTxjuW9rthShareA?e=VDcSu8 

[Ashish_Excel]

There is no file there.

[SamsonTruong]

Hi @Siddiq1212,

You can achieve this through creating a measure. Depending on whether or not these two tables are connected to a Date Table will change the approach slightly.

Assuming both of these tables are connected to the same Date Table, try the following DAX measure:

Total **bleep** Cost :=
VAR SelectedDate = MAX('Date'[Date])
VAR CumEarned =
    CALCULATE(
        MAX(TableA[**bleep** Earned Value Cost]),
        FILTER(
            ALL(TableA),
            TableA[Current Months Week] = SelectedDate
        )
    )
VAR CumForecast =
    CALCULATE(
        MAX(TableB[**bleep** Forecast Cost]),
        FILTER(
            ALL(TableB),
            TableB[Current Months Week] = SelectedDate
        )
    )
RETURN
COALESCE(CumEarned, 0) + COALESCE(CumForecast, 0)

In the case where these tables are disconnected and are not leveraging a Date Table. try the following DAX measure:

Total **bleep** Cost :=
VAR SelectedDate = MAX(TableA[Current Months Week])
VAR CumEarned =
    CALCULATE(
        MAX(TableA[**bleep** Earned Value Cost]),
        TableA[Current Months Week] = SelectedDate
    )
VAR CumForecast =
    CALCULATE(
        MAX(TableB[**bleep** Forecast Cost]),
        TREATAS({ SelectedDate }, TableB[Current Months Week])
    )
RETURN
COALESCE(CumEarned, 0) + COALESCE(CumForecast, 0)

Please let me know if either of these achieve your desired result.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session

[Siddiq1212]

Hi @SamsonTruong 

Thank you for your reply. I think I didn't get it properly.

Yes both tables are connected with date table " Current Month Week" column.

[SamsonTruong]

Hi @Siddiq1212 ,

Thank you for the additional context. Please try the adjusted DAX measure:

Total **bleep** Cost :=
VAR SelectedDate = MAX('Date'[Current Months Week])
VAR EarnedValue =
    CALCULATE(
        MAX('FactEarnedValue'[**bleep** Earned Value Cost]),
        FILTER(
            ALL('FactEarnedValue'),
            'FactEarnedValue'[Current Months Week] = SelectedDate
        )
    )
VAR ForecastCost =
    CALCULATE(
        MAX('FactForecast'[**bleep** Forecast Cost]),
        FILTER(
            ALL('FactForecast'),
            'FactForecast'[Current Months Week] = SelectedDate
        )
    )
RETURN
COALESCE(EarnedValue, 0) + COALESCE(ForecastCost, 0)

[Siddiq1212]

HI@SamsonTruong 

Sorry to bother again. I am not getting it. Tried as below.

[ABD128]

Hi @Siddiq1212 

Try with below DAX:

 **bleep** Combined Cost :=

VAR MaxEarned = CALCULATE(

MAX('TableA'[**bleep**  Earned Value Cost]),

ALL('TableA') )

RETURN IF( NOT ISBLANK('TableB'[**bleep** Forecast Cost]) && 'TableB'[**bleep** Forecast Cost] > 0, 'TableB'[**bleep** Forecast Cost] + MaxEarned,

BLANK() )

[Siddiq1212]

Hi @SamsonTruong 

Thank you for your reply. I think I didn't get it properly.

Yes both tables are connected with date table " Current Month Week" column.

[Siddiq1212]

Hi @SamsonTruong 

Thank you for your reply. I think I didn't get it properly.

Yes both tables are connected with date table " Current Month Week" column.

[Ashish_Excel]

Hi,

Share the download link of the PBI file.  Do you want the total to show up in a card visual?  If not, then clearly show the expected result.

[Siddiq1212]

Hi @Ashish_Excel 

Thank you for replying.

I am looking for a DAX to calculate cumulative sum of both columns to prepare a overall cumulative curve. The total in 31 Dec'23 will be 13,541,123+101,384,574.60 = 114,925,697.60

[Siddiq1212]

Hi @Ashish_Excel , @SamsonTruong ,

To simplify I need a DAX to calculate maximum value of column **bleep** Earned value cost & add to **bleep** Forecast cost. Something like below (if we talk about excel)

if (**bleep** Forecast Cost=0,"",**bleep** Forecast Cost+max(**bleep** Earned Value Cost)

[Ashish_Excel]

As requested ealier, share the download link of the PBI file.

[hnguy71]

Hi @Siddiq1212 

You can just use the sum function.

= SUM(**bleep** Earned Value Cost) + SUM(**bleep** Forecast Cost)

However, you'll need to make sure that the Date evaluation comes from a dimension table that's connected to both of your tables.