Hello everyone !
I am trying to get the cumulative sum of values from 2 different column using TOTALYTD. Sometimes the values from the 1st column are missing, in that case i want to use the values from another column form another table. The two tables are not directly related because there isn't any real link between the two tables and they are from 2 different APIs.
For now i tried using TOTALYTD which takes the values from the 1st column but doesnt switch to the other one when it is blank. I tried another methods where it did switch to the the 2nd column but instead of adding it to the pre-existing cumulative sum it created a new cumulative sum.
In short i want to be able to cumulate values from two differents columns, in different tables from different sources .
Here is an example of the data that i am using :
Table 1:
Location | Production 1 | Date |
LOC1 | 27568 | 07/04/2023 |
LOC1 | 29315 | 08/04/2023 |
LOC1 | 18379 | 09/04/2023 |
LOC1 | | 10/04/2023 |
LOC1 | | 11/04/2023 |
LOC1 | | 12/04/2023 |
LOC1 | | 13/04/2023 |
LOC1 | | 14/04/2023 |
Table 2:
Location | Production 2 | Date |
LOC1 | | 07/04/2023 |
LOC1 | | 08/04/2023 |
LOC1 | | 09/04/2023 |
LOC1 | | 10/04/2023 |
LOC1 | 13540 | 11/04/2023 |
LOC1 | 18379 | 12/04/2023 |
LOC1 | 23160 | 13/04/2023 |
LOC1 | 14220 | 14/04/2023 |
So when Production 1 is blank i need to take the value from Production 2 and if both are blank i need to add a zero.
For now i am using these measures, but 'Cumulative' doesn't work properlyn my cumulative total stays teh same when production 1 is blank. 'Values' does work and gives me either one of the production or a 0 if both are blank.
Cumulative =
TOTALYTD(
' Measures'[Values],
Dates[Date],
FILTER(
CALCULATETABLE(
SUMMARIZE('Dates', Dates[Date]),
ALLSELECTED(Table1),
ALLSELECTED(Table2)
),
ISONORAFTER(
'Dates'[Date], MAX('Dates'[Date]), DESC
)
)
)
Values =
IF(
ISBLANK(MAX(Table1[Production1])),
IF(
ISBLANK(MAX(Table2[Production2])),
0,
SUM(Table2[Production2])
),
SUM(Table1[Production1])
)
Do you know if it is possible to do it properly in dax ?
Thanks in advance,
MD
