Forum Discussion

mdufresne34's avatar
mdufresne34
Frequent Visitor
3 years ago

Cumulative Sum using different tables

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

2 Replies

    • mdufresne34's avatar
      mdufresne34
      Frequent Visitor

      Hello, Than you for your response, do you know if i could do this without time intelligence ? I tried using the link you provided but I couldn't adapt it to my problem