Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ColinG
Frequent Visitor

Calculating cumulative totals combining 2 tables with a single date axis

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.

ColinG_0-1698028347014.png

 

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])
    )
)
 
 
9 REPLIES 9
ColinG
Frequent Visitor

using ALL vs ALLSELECTED does not have any effect/impact

ColinG
Frequent Visitor

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
Frequent Visitor

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
Frequent Visitor

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
Frequent Visitor

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

ColinG
Frequent Visitor

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
Frequent Visitor

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.

 

ColinG_0-1698032134188.png

 

@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
Super User
Super User

@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

amitchandak_0-1698031364251.png

 

 

 

Live model

 

amitchandak_1-1698031397088.png

 

 

Excel join with live/direct date table

amitchandak_2-1698031430063.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.