Difference value between two sheets data without having any relationship

[kavya]

Hi All,

Help me in finding out the difference value. Report source is the Excel sheet having Current day data in one tab & Previous day data in another tab. Scenario is i have to findout the difference between the current day - previous day. The is no relationship between these two tabs.

Current Day Sheet Data:

Current Day  Country  Price

3/7/2017       US           100

Previous Day Sheet Data:

Previous Day  Country  Price

2/7/2017       US           25

I want the difference between these two even though not having the relationship between these tabs.

Thanks

Kavya

[dearwatson]

Hi Kavya,

Lots of ways to do this... I would create a calendar table (date dimension) with two columns - date and previous date (current date - 1 day) then create a relationship from the 'current sheet'[current date] to the calendar[date] and another relationship from the 'previous day'[previous date] to the calendar[previous date] column...

Then when you select the date in the calendar it will filter the current and previous date sheets correctly.

try this 🙂

[Zubair_Muhammad]

@kavya

Try this calculated column in CurrentDay Table

Difference =
CurrentDayTable[Price]
    - LOOKUPVALUE (
        PreviousDayTable[Price],
        PreviousDayTable[Country], CurrentDayTable[Country],
        PreviousDayTable[Previous Day], CurrentDayTable[Current Day] - 1
    )

[kavya]

Getting the exact value of the current day price not the difference.

[Anonymous]

Hi @kavya,

If you have two data set for the same, then you can follow the steps to achive it.

1. Open the Edit query window.
2. Add Index column to CurrentDate and Previous Date data sheet. Make sure both the index column starts with the same number in order to use further into merge.
3. Merge Both the datset using the Index Column.

And then you can get both the current date and the previous date result in the same Row and you can easily subtract the values.

[kavya]

Hi Muhammad,

I am getting error when i used the formula. Below is the error

The table of multiple values was supplied where a single value expected.

Thanks

Kavya

[v-yulgu-msft · Accepted solution]

Hi @kavya,

---

 I am getting error when i used the formula. Below is the error

The table of multiple values was supplied where a single value expected.

---

In your PreviousDayTable, does [Price]  have unique values per date per Country? If not, you can do a SUMMARIZE (new a calculated table) first, then, try Zubair_Muhammad's formula.

New Table =
SUMMARIZE (
    PreviousDayTable,
    PreviousDayTable[Previous Day],
    PreviousDayTable[Country],
    "Price", SUM ( PreviousDayTable[Price] )
)

Refer to this 'New Table' rather than PreviousDayTable when creating calculated column.

Difference =
CurrentDayTable[Price]
    - LOOKUPVALUE (
        'New Table'[Price],
        'New Table'[Country], CurrentDayTable[Country],
        'New Table'[Previous Day], CurrentDayTable[Current Day] - 1
    )

Regards,

Yuliana Gu