Calculating cumulative totals combining 2 tables with a single date axis

[ColinG]

Some details about my model

• I am not able to share the actual pbix file due to IRM concerns.
• I am combining a live/direct query to a published Power BI dataset (forecast/plan spend) with a single imported spreadsheet to show actual spend.
• The live/directy query Power BI dataset contains many tables etc, a fairly involved data mode.
• For this example, we can call the Power BI dataset's primary table "Live_Table1". This table contains an init_Date column of type Date. The dataset uses Date Hierarchy model which I can not change - not within my control.
• For this example, we can call the spreadsheet "Import_Table2". The spreadsheet contains a Date_EoM column of type Date. I don't typically use Date Hierarchy but I can change this if it helps solve the issue.
• I have a "proper" date table called Dim_Date that I've used for testing  and troubleshooting relationships etc

My issue

• I am fairly experienced in Power BI. I have done hours of reasearch on this topic and tried many different methods, to no avail.
• I am able to create a cumulative total measure as you'd expect on either Live_Table1 or Import_Table2. When I combine the 2 measures into a single visual for charting on a common date axis, using any variety of lookups and relationships.... one or the other of the totals fails.
• I need to combine the running totals on a single date axis for charting/etc.
• As you can see below, depending on which date column i choose... one or the other cumulative total is correct per month.

This is a sample of my logic... using the Dim_Date table.. but I have tried numerous iterations of what to use for the MAX date comparison.

Total Investment LE = CALCULATE(

    SUM(Import_Table2[Amount]),

    FILTER(

        ALLSELECTED(Import_Table2),

        Import_Table2[Data Type]="LE"

        && 'Import_Table2[Date_EoM] <= MAX(dim_Date[End of Month])

    )

)

[ColinG]

using ALL vs ALLSELECTED does not have any effect/impact

[ColinG]

If I change the ALLSELECTED to the following, it returns an error. The error is tied to a calculated column in the same table. I do not have access to the logic of the calculated column... i can only query the Power BI dataset 

Actual Running Total 3 = CALCULATE(

    SUM('Direct Query'[Actual Value]),

    filter(

        ALLSELECTED('Direct Query'),

        'Direct Query'[init_DATE] <=max(dim_Date[End of Month])

        ))

[ColinG]

this measure DOES calculate a running cimulative sum. It is using the same common date table...

Total Investment LE = CALCULATE(

    SUM(Spreadsheet[Amount]),

    FILTER(

        ALLSELECTED('Spreadsheet'),

        Spreadsheet[Data Type]="LE"

        && 'Spreadsheet'[Date_EoM] <= MAX(dim_Date[End of Month])

    )

)

[ColinG]

this measure does not calculate a running cumulative sum. It is using the common date table... 

Actual Running Total 3 = CALCULATE(

    SUM('Direct Query'[Actual Value]),

    filter(

        ALLSELECTED('Direct Query'[init_DATE]),

        'Direct Query'[init_DATE] <=max(dim_Date[End of Month])

        ))

[ColinG]

my common date table is "dim_Date" and is used above

[ColinG]

i need to track the 2 running/cumulative totals independently. My desired outcome is for the last row (12/31/2023) to show the total for each column in a single visual.

The final row in the table (excluding the visual's Total row) would read...

Date_EoM : 12-31-2023

Actual Running Total: $13,327,500.43

Total Investment LE: $15,058.871.00

... 

As illustrated in the screenshot in initial posting, I am able to get one or the other to appear correctly depending on which Date column is select for the visual

[ColinG]

Thank you for the reply... as added info, this is my data model as it relates to this issue. I have created a commone dim_Date table between the imported Spreadsheet and the DirectQuery to a published Power BI dataset.

[amitchandak]

@ColinG , All measures and visual,s and slicers should use a common date table

Total Investment LE = CALCULATE(
SUM(Import_Table2[Amount]),
Filter(Import_Table2,
Import_Table2[Data Type]="LE")

, FILTER(
ALLSELECTED('Date')
'Date'[Date] <= MAX('Date'[Date])
)

as per need use all in place of allselected
)

[amitchandak]

@ColinG , using a common date table in live/direct query model, I am able to create a cumulative measure along with data from excel

Combined = CALCULATE([Net] + SUM(Purchase[Purchase_amount]), WINDOW(0,ABS,0,REL,all('Date'[Date]), ORDERBY('Date'[Date])))

//Only SUM(Purchase[Purchase_amount]) is from import mode. Rest from Direct query Dataset

Live model

Excel join with live/direct date table