How to iterative sum across unrelated tables

[bslima_1000]

Hello all,

I am trying to make a report that compares forecasts/budgets and actual numbers , in order to do so I have two tables with the exact same content, which one being filtered by a slicer containing all the months avaiable to be selected for that table.

The problem emerged when I had to perform an iterative calculation tha depends on both table's content, since which one of them have it's own filter context my SUMX function is bringing the wrong results.

Here the link with a sample of what I'm trying to do: PBIX & Excel Base 

Thank you to everyone in advance.

[VijayP]

@bslima_1000 

https://drive.google.com/file/d/19MMb4o0dJVaGGsYSSzZuFnQTq9Sbl_XM/view?usp=sharing

Is this you want!? Please share your kudoes

[bslima_1000]

Thank you for your response, @VijayP. However the solution proposed by you is not quite what I want.

Let me explain better:

1 - I have two table with the exact same content, one is called AOC_1 anf the other is AOC_2:

2 - In order to compare them in terms of Adj Sales I have to apply the following mathmatical formula:

Adj Sales Compare = (AOC_2[Adj Sales] at T1 - AOC_1[Adj Sales] at T0) / AOC_2[Volume] at T1

3 - Applying this formula on Excel  (Link to Excel file ) is easy, and returns this results:

4 - However, in Power BI things are not so simple. Look when I try to do the same as I did in Excel what happend:

The measure I used:

Adj Sales = (SUM(AOC_2[Adj Sales]) - SUM(AOC_1[Adj Sales])) * SUM(AOC_2[Volume])

As you can see the cause of the issue is that Power BI is not iterating the formula row by row and I can't perform a SUMX across different unrelated tables with distinct filter contexts.

Do you know how to solve this?

Thank again for your previous answer.

[CNENFRNL]

Hi, @bslima_1000 , DAX is good at iteration, a measure can be authored this way based on your mockup dataset,

Adj Sales = 
SUMX (
    VALUES ( PN[PN] ),
    SUMX (
        VALUES ( FM[FM] ),
         ( [Sales] - CALCULATE ( [Sales], DATEADD ( AOC_1[Month], -1, MONTH ) ) )
            * CALCULATE( MAX ( AOC_1[Volume] ) )
    )
)

btw, you can simplify your data model by keeping only one sales data table. pls refer to the attach file.

[bslima_1000]

Thank you a lot for the tips @CNENFRNL, but following your proposed solution the user would not have the option to compare different periods of time. I've implemented the two tables solution in order to adress this very issue.

Let me be more clear:

The report needs to be able to compare any database time setted by the user into the slicers. On the example we're talking about you covered only the possibility of comparing the selected month versus the previous one. But I have multiple comparisons to present as you can see on the bellow images.

1 - Compare one month with the previous one:

2 - Compare one quarter with another

 3 - Compare completly random months (or set of months)

 Do you know if that is even possible?

Again, thank you a lot for your contribution.

[CNENFRNL]

Hi, @bslima_1000 , I'm wondering how you would compare those ramdonly-chosen months; furthermore, what if 2018/01-03 (3 options) are chosen in Month slicer with 2019/04-08 (5 options) being chosen? How can you control and compare them?

[bslima_1000]

You asked good questions.
The randomly selected month don't make sense, I just mentioned that the user would have that option in order to exemplify that the possibilities of combinations are numerous. To explain better, in this report the user would have the option to compare the actual results of a given month (or even YTD until this same month) with the same period for last year, or with the same period for two years ago, or even compare the variances with the last year budget and vice versa.
Due to the flexibilities to compare any data range of one database with the another comes my request of a way to use iterative functions across diffenrent tables which one having it's own filter context.

Does anyone know how to solve this?

[CNENFRNL]

@bslima_1000 

Like what I did, add another slicer with intervals from 1 to 24 ( or as many as you want ), then you can compare the data from Month slicer with those from 1/2/3/...24 months ago.

Pls refer to the attached file for details.