Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ergocorp
Helper II
Helper II

Using another table's values in calculate

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:

 

ergocorp_0-1701792301383.png

 

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?

 

8 REPLIES 8
ergocorp
Helper II
Helper II

@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:

ergocorp_2-1703172628089.png

Then for the next week:

ergocorp_3-1703172937279.png

and the next week:

ergocorp_5-1703173433318.png

 

So far so good, but:

ergocorp_7-1703173729849.png

 

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

 

ergocorp_8-1703173908900.png

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
Resolver II
Resolver II

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)?

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:

 

ergocorp_0-1701800310535.png

Nor reverting that and changing the measure:

ergocorp_1-1701800432805.png

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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
Resolver II
Resolver II

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)

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

 

ergocorp_0-1701794330234.png

 

So, looking at the relationship:

ergocorp_1-1701794418848.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors