Using another table's values in calculate

Anonymous Hi all,

So I'm still stuck, I've tried so many ways and means. The best thing I can do is give a more detailed understanding and an example of what it is I'm looking for.

So I have a lot of ledger data. One of they key measurements on it is due date. My company does everything by financial week. That starts on a Sunday. The weeknum function uses the ,17 end part.

I get a new report on a monday for the previous week (I've done work to create an accurate week number, but that is moot to what I need). This means some of the data is duplicated, but i''ve done some work on ensuring that isn't a problem using the file name.

This means that let's say I get a report in week 46, and an amount is due in week 50 (due in 4 weeks time) , it should remain that amount on the week 47 report, due in week 50, but now due in 3 weeks time. This should remain the same on the week 48 report, due in week 50 which means that it is due in 2 weeks, and again the same on the week 49 report due in week 50, which would be 1 week.

However these aren't the same in many cases. This could be anything from credit notes, matched payments on account or the other way, such as late invoicing etc. I've been tasked with showing these differences, so we can see where the changes have come from to allow investigation.

I've created a table visualisation that outputs the supplier name, but also measures that show how many weeks in the future a payment would be due. This all sliced by the financial week number (using a seperate numbers table).

Rather than try and build dynamic column headers, I've created card visualisations that go over those headers that have what week it would refer to rather than saying 4 weeks (after the week set by the slicer), 3 weeks (after the week set by the slicer) etc.

I've filtered the suppliers on the example pictures, to show what I mean:

Then for the next week:

and the next week:

So far so good, but:

That has dropped by £2M. In week 49, we actually paid that supplier:

The amount of difference should therefore be investigated. What I need to visualise is the sizes of those differences so we can reorder the table and find those big hitters (right now we just have the total swing).

As such I'm trying to create measures that highlight those differences.

4 weeks in the future from actual - 3 weeks in the future from actual 

3 weeks in the future from actual - 2 weeks in the future from actual 

2 weeks in the future from actual - 1 week in the future from actual 

1 week in the future from actual  - Actual

4 weeks in the future from actual - Actual

My current measures for those future weeks are built like this:

M 4w = -CALCULATE([M Total (SAP)],VALUE('Data - SAP'[C How Many Weeks into the Future?])=4)

Where the Measure [M Total (SAP)] is it all summed

and the column 'Data - SAP'[C How Many Weeks into the Future?] is literally a calculation of the week number it is due minus the week number a report is looking at.

As such I also have

M 3w = -CALCULATE([M Total (SAP)],VALUE('Data - SAP'[C How Many Weeks into the Future?])=3)

M 2w = -CALCULATE([M Total (SAP)],VALUE('Data - SAP'[C How Many Weeks into the Future?])=2)

M 1w = -CALCULATE([M Total (SAP)],VALUE('Data - SAP'[C How Many Weeks into the Future?])=1)

1. It's a negative sign in front of the calculate as the data is all debits and I just inverted them.

NB 2. The actuals come from a different data source.

Like I said at the start of this I have tried many things, but I feel the closest I have got for the difference between 4 weeks out week 45 to 3 weeks out week 46 (which should actually equal 0 in this example) is:

M Diff 4 - 3 (Staggered) =

-CALCULATE([M Total (SAP)],

'Data - SAP'[C Adj. Week]='Data - SAP'[C Due Week]-4,

VALUE('Data - SAP'[C How Many Weeks into the Future?])=4)

-

-CALCULATE([M Total (SAP)],

'Data - SAP'[C Adj. Week]='Data - SAP'[C Due Week]-3,

VALUE('Data - SAP'[C How Many Weeks into the Future?])=3)

This unfortunately only gives me the difference between 4 and 3 weeks out in relation to the week selected by the slicer. In fact, the first filter there on the calculates seem be irrelevant.

Any ideas?