Forum Discussion
Advocate IIRevenue and Expenditure Table Variance
I tried to simulate a case like yours, and this is the results...
Advocate IIAs I look at it, this is indeed the case. When a location may have moved in a previous operating year, it kept the same number but took on the name of where it moved. For accounting purposes, the name in effect for that operating year remained instead of being overridden even if it was in the same city and just moved to a different address.
Then I have discovered that two of our locations have no code at all. So truly now the only thing consistent between both tables is the name of the location within an operating year. What do I do with this?
Solution Specialist- BILASolution
I tried to simulate a case like yours, and this is the results...
- JMWDBA
That becomes my conundrum. I cannot obtain clearance to transmit the actual file. Is it possible to leave out the Location Description and just use the Codes? The codes I know are unique but something like Jacksonville - Main could have once been called Jacksonville - Mayport last year and the year before that it was Jacksonville - Matthews. So the name changed but the code always remained the same.
- JMWDBA
Yess!!! Its 99% there. Now that I am no longer getting errors, what I notice its doing for the Sum of Revenue is taking the SUM across all 5 years worth of data I have in the revenue file which totals $527,899,365.06 instead of revenue just for the corresponding location. It's also doing the same thing for Expenses.
I am going back through the pbix you provided to see if I missed something because I see that yours actually takes it for the specific location.
- JMWDBA
Ok I see why its doing totals for both instead of by location. It wont establish the relationship because it needs at least one of the tables to have distinct values.
- JMWDBA
Removing the location name altogether made everything work!!!:smileyvery-happy:
- BILASolution
Excellent :smileyhappy: