Using another table's values in calculate

[ergocorp]

Hi, I am hoping someone can help. We are trying something with my company's cash forecasting.

We get a report every week that lists every invoice that has come in on the ledger. These willl have due dates, so we can establish when a supplier is due to be paid X-weeks into the future.

It's not as simple as that. My company has a turnover of over £10bn and we process literally thousands of invoices a day. This is a round about way of saying that if we are due to pay a supplier 2 weeks in the future on a report, compared to what we are due to pay the same supplier on the next week's report in 1 week in the future are different. I've been tasked with establishing what those differences are.

I've created a way to have the report visualised using a drop down slicer by week number by creating a table of week numbers and having a relationship through the two tables, so it looks like this:

So I need to work out measures that take the data from 1 week and subtract the data from a second week, all relative to the week number of the report. I am struggling with the filter on the calculate.

Measure 1 week out = CALCULATE([Measure Total Value],Column Adjusted Week = 'Week'Week Number +1)

I've altered and lengthened the names of some of my measures and columns, the plan is to also create:

Measure 2 week out = CALCULATE([Measure Total Value],Column Adjusted Week = 'Week'Week Number +2) 

and do a measure

Measure 2w vs 1w = [Measure 2 week out] - [Measure 1 week out]

Then throw that in the above table vis. as a new column.

The problem I'm having is with the ='week'week number etc. part. As it is from another table, it won't go into the CALCULATE. Any idea?

[ergocorp]

@v-yiruan-msft 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?

[Jamie_Scott]

The relationship is on the column 'C Week of Report' in the relationship, vs. on 'C Adj Week' in the measure, so there is indeed no relationship between those two columns. Can you adjust the relationship or the measure (depending on which is appropriate)?

[ergocorp]

I couldn't help myself, I logged on to check while I have ordered a delivery lol. That didn't work, both changing the column relationship:

Nor reverting that and changing the measure:

Reversing the relationship isn't allowed as it causes an ambiguous path to another table, and changing the cardinality isn't allowed either.

[v-yiruan-msft]

Hi @ergocorp ,

Did you create any inactive relationship between the table SAP[C Week Of Report] and Week[Week Number]? Please update the formula of measure [M1wx] as below and check if it can return your expected result...

M1wx =
CALCULATE (
    [M Total],
    FILTER ( SAP, SAP[C Week Of Report] = MAX ( Week[Week Number] ) + 1 )
)

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format, expected result and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[ergocorp]

Thanks, it hasn't worked. I don't actually believe what I want to show is acctually correct either.

I can't create and attach an example - this is extremely bespoke, I'm not sure how I would trim it down and change things to not show sensitive data.

[ergocorp]

I may be able to change it to the adjustment column. I'll have to check tomorrow (I've logged off now. - it's 5pm here). Thanks 

[Jamie_Scott]

Hi,

If I understand you correctly, you can use the RELATED function in this instance, something like:

Measure 1 week out = CALCULATE([Measure Total Value], 'Column Adjusted Week'[Week Number] = RELATED('Week'[Week Number]) + 1)

[ergocorp]

Thanks for the quick response. Unfortunately that didn't work - I get the following message:

So, looking at the relationship: