Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
LOCATION | VOLUME | OPERATION DATE | PLANT_KEY | PRODUCT |
XXX | 1000 | 26/06/2022 | A_1 | 1 |
XXX | 1000 | 27/06/2022 | A_1 | 1 |
XXX | 1000 | 28/06/2022 | A_1 | 1 |
XXX | 1000 | 29/06/2022 | A_1 | 1 |
XXX | 1000 | 01/07/2022 | A_1 | 1 |
XXX | 1000 | 02/07/2022 | A_1 | 1 |
XXX | 1000 | 03/07/2022 | A_1 | 1 |
XXX | 1000 | 04/07/2022 | A_1 | 1 |
XXX | 1000 | 05/07/2022 | A_1 | 1 |
YYY | 2000 | 01/05/2022 | B_1 | 1 |
YYY | 2000 | 01/05/2022 | B_1 | 1 |
YYY | 2000 | 01/05/2022 | B_1 | 1 |
YYY | 2000 | 01/05/2022 | B_1 | 1 |
YYY | 2000 | 01/05/2022 | B_1 | 1 |
YYY | 2000 | 01/05/2022 | B_1 | 1 |
ZZZ | 1000 | 26/06/2022 | C_1 | 1 |
ZZZ | 1000 | 27/06/2022 | C_1 | 1 |
ZZZ | 1000 | 28/06/2022 | C_1 | 1 |
ZZZ | 1000 | 29/06/2022 | C_1 | 1 |
ZZZ | 1000 | 01/07/2022 | C_1 | 1 |
ZZZ | 1000 | 02/07/2022 | C_1 | 1 |
ZZZ | 1000 | 03/07/2022 | C_1 | 1 |
ZZZ | 1000 | 04/07/2022 | C_1 | 1 |
ZZZ | 1000 | 05/07/2022 | C_1 | 1 |
TABLE B
LOCATION | START INTERNAL OPERATION | PLANT_KEY |
XXX | 30/06/2022 | A_1 |
YYY | 01/01/2022 | B_1 |
ZZZ | 03/07/2022 | C_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
LOCATION | Tot Volume to be calculated |
XXX | 5000 |
YYY | 12000 |
ZZZ | 3000 |
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!
Solved! Go to Solution.
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
)
)
)
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!
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 Number | Date | Major Spend |
123 | 01/01/2022 | 7,000 |
123 | 02/01/2022 | 10,690 |
123 | 03/01/2022 | 1,410 |
456 | 02/01/2019 | 15,314 |
456 | 03/01/2019 | 16,414 |
456 | 04/01/2019 | 1,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 Number | Property Name | Start Date |
123 | Apartment A | 08/01/2022 |
456 | Apartment B | |
789 | Apartment C | 12/01/2022 |
122 | Apartment D | 11/01/2021 |
133 | Apartment E | 05/01/2022 |
144 | Apartment F | 08/01/2022 |
155 | Apartment G | |
166 | Apartment H | 11/01/2022 |
177 | Apartment I | 02/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
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.
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.
Remaining - is the original calculation
Test1 - is the 2nd calculation you just provided.
This is what happens when I filter by Date,
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
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.
Here is the measure -
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].
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!
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!
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
)
)
)
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |