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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FP220388
Frequent Visitor

SUM VALUES BASED ON A DATE IN ANOTHER TABLE NOT RELATED

Hi All,

i'm struggling with a dax formula to calculate the amount of sales volume based on a date in another table not directly related.

 

in table A I have some data like below:

 

TABLE A

LOCATIONVOLUMEOPERATION DATEPLANT_KEYPRODUCT
XXX100026/06/2022A_11
XXX100027/06/2022A_11
XXX100028/06/2022A_11
XXX100029/06/2022A_11
XXX100001/07/2022A_11
XXX100002/07/2022A_11
XXX100003/07/2022A_11
XXX100004/07/2022A_11
XXX100005/07/2022A_11
YYY200001/05/2022B_11
YYY200001/05/2022B_11
YYY200001/05/2022B_11
YYY200001/05/2022B_11
YYY200001/05/2022B_11
YYY200001/05/2022B_11
ZZZ100026/06/2022C_11
ZZZ100027/06/2022C_11
ZZZ100028/06/2022C_11
ZZZ100029/06/2022C_11
ZZZ100001/07/2022C_11
ZZZ100002/07/2022C_11
ZZZ100003/07/2022C_11
ZZZ100004/07/2022C_11
ZZZ100005/07/2022C_11

 

TABLE B

LOCATIONSTART INTERNAL OPERATIONPLANT_KEY
XXX30/06/2022A_1
YYY01/01/2022B_1
ZZZ03/07/2022C_1

 

TABLE A and TABLE B are not related and cannot be directly, anyway they are linked to another table (Table C) based on the PLANT_KEY column. 
The relationship between Table C and Table A is 1:*

The relationship between Table C and Table B is 1:*

 

THE EXPECTED OUT PUT IS

LOCATIONTot Volume to be calculated
XXX5000
YYY12000
ZZZ3000

 

So basically I want to SUM the Volume for each LOCATION just after the START INTERNAL OPERATION DATE and not considering in the calculation the previous Volume.

 

Please Help!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @FP220388 
Here is a sample file with the solution https://www.dropbox.com/t/d2ggzrOyDorwrliZ

Total Volume = 
SUMX ( 
    VALUES (TableB[LOCATION] ),
    CALCULATE (
        VAR BDate = SELECTEDVALUE ( TableB[START INTERNAL OPERATION] ) 
        RETURN
            CALCULATE ( 
                SUM ( TableA[VOLUME] ),
                TREATAS ( VALUES ( TableB[LOCATION] ), TableA[LOCATION] ),
                TableA[OPERATION DATE] >= BDate
            )
    )
)

View solution in original post

14 REPLIES 14
JodieO
Frequent Visitor

Hi,  Sorry to open an old thread, positing this again because I am not sure I posted correctly the first time.

 

I have a very similar situation with two unrelated tables.  I also need to SUM Spend from (TableA) for several Properties just after a start date from (TableB).  Both tables are linked to a third table based on a column PID for each property.   TableA and TableB are also both linked to a date table.

 

The problem when I filter for 2023 my spend is blank.  How do I get this to start with the provided start date (TableB) and sum through the most current date, from (TableA)?

 

Thank you for your help!

@JodieO 

Where does the date filter come form? What the date based relationships? What is the granularity of the start date column? Can you provide some sample data along with some screenshots of your data model and report?

Table A

Property NumberDateMajor Spend
12301/01/20227,000
12302/01/202210,690
12303/01/20221,410
45602/01/201915,314
45603/01/201916,414
45604/01/20191,306

 

Table A is a very large table containing the Major spending each month for each property, property numbers are repeated in this table.  

Table A is linked with a 1:* relationship by the Property Number column to a Key_table which has a Property Number and Property Name columns.  The Date column is linked with a 1:* relationship to a separate Date_Table.

 

Table B

Property NumberProperty NameStart Date

123

Apartment A08/01/2022
456Apartment B 
789Apartment C12/01/2022
122Apartment D11/01/2021
133Apartment E05/01/2022
144Apartment F08/01/2022
155Apartment G 
166Apartment H11/01/2022
177Apartment I02/01/2023

 

Table B Lists each property number only once, the Start Date in this table will change as newer general ledgers are pulled.  Table B is also linked by a 1:* relationship to the Key_table through the Property Number column, the same Key_table as Table A. 

The column Start Date is linked through a 1:* relationship to the same separate Date_Table.

 

Table A and Table B are not related directly.

 

What I am trying to accomplish:

I need this to sum the Remaining total of Major Spend from (table A) beginning after the Start Date from (Table B) and if there is no Start Date it should just sum Major Spend. 

So, if I add a date filter I will get all of the Major spending for the filtered year for properties that do not have a start date, and for the properties that have a start date, it will sum starting the month after the Start Date and into the future.

This is the formula I am using to sum the Major Spend for properties after the Start Date

 
Remaining =
SUMX(
VALUES('Table B'[Property Number]),
CALCULATE(
VAR SDate = SELECTEDVALUE('Table B'[Start Date])
RETURN CALCULATE(
SUM('Table A'[Major Spend]),
TREATAS(VALUES('Table B'[Property Number]),'Table A'[Property Number]),
'Table A'[Date] > SDate)))


This appears to work until I start filtering. I am not sure what is wrong but when I filter my date for 2023 I can see from Major Spend is over 3 million.  My remaining only shows $2,900, for the one property that has a start date in 2023.  

Thank you so much for any help you can provide.

@JodieO 
In your report you should be slicing by the property name or number fro the common dim table right? Can share something that helps better picturing the shape of the report? I think there is no need to use TREATAS in this case as the slicing by column filters both tables. Perhaps what might create trouble is including the start date column in the visual. If you need to see the starting date in the visual then you need to have only as a measure (like SELECTEDVALUE). Please advise.

I believe this should work unless I'm missing something

Remaining =
SUMX (
    VALUES ( 'dimTable'[Property Number] ),
    CALCULATE (
        VAR SDate =
            SELECTEDVALUE ( 'Table B'[Start Date] )
        RETURN
            CALCULATE ( SUM ( 'Table A'[Major Spend] ), 'Table A'[Date] > SDate )
    )
)

Thank you but unfortunately, this did not work.

JodieO_0-1678302350324.png

Here is how my tables are linked.  Yes, I am pulling in the Property Number and Property Name from the Table Key in my report.  I am creating the measure in Table A, not sure if it makes a difference where the measure is created.

JodieO_1-1678302667472.png

Remaining - is the original calculation 

Test1 - is the 2nd calculation you just provided.

This is what happens when I filter by Date,

JodieO_2-1678302875321.png

It appears the new calculation (Test1) is not taking into consideration the start date and the original calculation (Remaining) is not pulling if there is no start date for that year.

Thanks

 

 

 

@JodieO 

Delete the relationship between'Date'[Date] and TableB[Start Date]  

In your description you have mentioned that in TableB the property id is listed only once. Would you please reconfirm. Can you try a test measure = SELECTEDVALUE ( TableB[Start Date] )

I have deleted this relationship.  That is correct the property Id is only listed once in Table B.

JodieO_0-1678314716180.png

Here is the measure -

JodieO_1-1678314916148.png

Thank you so much for helping me, I have been struggling with this for days.

@JodieO 
Great! But how are you planning to deal with the blank start days?

Also I guess it would be best in terms of performance to avoid unnecessary context transition by using the following code

Remaining =
SUMX (
    'Table B',
    CALCULATE (
        SUM ( 'Table A'[Major Spend] ),
        'Table A'[Date] > 'Table B'[Start Date]
    )
)

I am not sure how to deal with them, I could maybe populate an earlier date like 01/01/2019 in all the properties with a blank start date.  Table B is not a very big table total of 260 properties so it wouldn't take long. Table A is pulled from a data pump and is filtered to start with 2019 information, so 2019 would be the earliest it could sum.

 

The above formula does not work, it does not recognize 'Table B'[Start Date].

 

JodieO_0-1678318487648.png

Thank you

@JodieO 
In this case Blank is just fine. Please try

Remaining =
SUMX (
    'Table B',
    VAR StartDate = 'Table B'[Start Date]
    RETURN
        CALCULATE ( SUM ( 'Table A'[Major Spend] ), 'Table A'[Date] > StartDate )
)

Thank you, this works!

JodieO
Frequent Visitor

Hi, I have a very similar situation with two unrelated tables.  I also need to SUM Spend from (TableA) for several Properties just after a start date from (TableB).  Both tables are linked to a third table based on a column PID for each property.   TableA and TableB are also both linked to a date table.

 

The problem when I filter for 2023 my spend is blank.  How do I get this to start with the provided start date (TableB) and sum through the most current date, from (TableA)?

 

Thank you for your help!

FP220388
Frequent Visitor

Hi @tamerj1 @ it works perfectly! 
Thank You!

tamerj1
Super User
Super User

Hi @FP220388 
Here is a sample file with the solution https://www.dropbox.com/t/d2ggzrOyDorwrliZ

Total Volume = 
SUMX ( 
    VALUES (TableB[LOCATION] ),
    CALCULATE (
        VAR BDate = SELECTEDVALUE ( TableB[START INTERNAL OPERATION] ) 
        RETURN
            CALCULATE ( 
                SUM ( TableA[VOLUME] ),
                TREATAS ( VALUES ( TableB[LOCATION] ), TableA[LOCATION] ),
                TableA[OPERATION DATE] >= BDate
            )
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors