Actual Vs Budget report

[deeksha]

Hi all,

I Have two tables, the first one contains the budget transactions,their date and main accounts with dimensions and the second one contains the actual transactions, transaction dates and their main accounts with dimensions.

The problem is when i am using a slicer to display the transactions according to their dates, when i am using the dates of budget table it is showing only the correct budget transactions and when i am using actual date in slicer it is filtering out only the actual transactions.

I also tried to create a calendar date table and made a relationship with the other two tables but it does not work.

I want a date slicer which filters out both the actual transactions and budget transactions accordingly, which date should i use?

Any help is really appreciated.

[Anonymous · Accepted solution]

Checked your PBIX file.

You are not getting the results because there is a circular/ambiguous relationship. As a result, one of the relationships from the Calendar table is inactive. Refer to the image below.

First, you should delete the relationship between the field "Display Value"  in both the table. 

Then edit the inactive (dotted line) relationship from the Calendar table and make it active.

It will resolve the issue.

Then create a separate table for "Display Value" and connect the same to the Display Value field for both the tables. This will avoid a circular relationship. Refer to the image below

You should then use the "Display Value" field from the new table in the slicer.

Conceptually that's about it. 

[Anonymous]

"I also tried to create a calendar date table and made a relationship with the other two tables but it does not work."

Creating a calendar table and creating relationships with the transaction tables is the right way to do. Want more details about why it didn't work. What was the error?

[deeksha]

Hi @Anonymous 

I have created a calendar date table with a single column of date and create one to many relationship with both the actual table and budget table but when i am choosing any date from the slicer, no transactions is being displayed.

here is the link of my sample data

https://drive.google.com/open?id=1vBWi_KYRaZpP6TphpVHoX1HQp8zZ-XKr

[Anonymous · Accepted solution]

Checked your PBIX file.

You are not getting the results because there is a circular/ambiguous relationship. As a result, one of the relationships from the Calendar table is inactive. Refer to the image below.

First, you should delete the relationship between the field "Display Value"  in both the table. 

Then edit the inactive (dotted line) relationship from the Calendar table and make it active.

It will resolve the issue.

Then create a separate table for "Display Value" and connect the same to the Display Value field for both the tables. This will avoid a circular relationship. Refer to the image below

You should then use the "Display Value" field from the new table in the slicer.

Conceptually that's about it. 

[deeksha]

Hey @Anonymous Thank you soo much for the reply, 

As you said remove the direct relationship between the actual table and budget table and should use a seprate display value table, but both the tables have different display values, which one should i use?

Also, regarding the slicer, i need a date slicer which can show the transactions according to date for both the actual amount and budget amount, i can not use the display values for it.

[Anonymous]

Actually, I didin't understand the purpose of connecting the "DisplayValue" field of both the tables if both the tables are gonna have different values in "DisplayValues" field. Creating such a relationship will result in you not getting any data in your visual.

But I saw a visual in your report like this...

What was the purpose of this visual, if both the table have different values in "DisplayValue" field. I have checked the values in "DisplayValue" field in both the tables. Actually you have 32939 unique values in one table and only 4 unique values in the budget table. Again only 2 values are common in both the tables. In such a scenario, using the DisplayValue field in a table matrix and plotting the actuals and budget against that is not going to be much useful. 

[deeksha]

@AnonymousAgain, Thank you soo much for the replies

Yes, you are right

But i am using the display value in my table visual because i need the actual amount and the budget amount for each account(display value), the display value contains the main accounts along with the financial dimension.

And i created the relationship between them so that i can get the budget amount and actual amount together in the table for each display value.

Anyways the relation was of no use beacause i have to calculate the variance amount also,which i was unable to calculate because of the many to many relationship between the both display values.

Correct me if i am doing it in wrong way.

I have deleted the relation between both the display values and created a separate display value table using the display values of actual table(dimensionattribute) and activated the dotted calendar relationship.

[Anonymous · Accepted solution]

Looks okay. But my suggestion is that you create a calculated table for "Display Values" using a formula like this...

DisplayValues = DISTINCT(
                    UNION(ALL('BudgetTransactions (2)'[DisplayValue]),
                            ALL(DimensionAttributeValueCombinations[DisplayValue])
                    )
                )

This will pick up the unique display values from both the tables and union them to give you all the distinct display values from both the table. It is up to you based on what you want.

Once you have a calculated table like I mentioned above with all the distinct DisplayValues from both the table, you will be able to calculate the Variances between Budget and Actual easily because the relationships will become 1 to Many.

[deeksha]

@Anonymous 

Thank you soo much for your valuable replies.

I was creating the calculated column for display values as you said but i am getting this error.

[Anonymous]

It is not a "Calculated Column". It is a calculated "Table".

You are trying to use the DAX code in a "New Column" option. But the code is for "New Table"

[deeksha]

@Anonymous 

Hey Everything else is working fine but when i am using any date from the slicer, its still not showing any data,

sorry in advance if i am making mistakes.

[Anonymous]

Please send me the .pbix file.

[deeksha]

@Anonymous 

here is the link to my sample data

https://drive.google.com/open?id=1cESCi7puU8aWpGxDXCnd2w7VwzKHieEh

[Anonymous]

I found the problem.

In your calendar table, every date is in date/time format with the time portion set to "00:00:00" hours.

But both in your budget and actuals tables, again date field is in date/time format, but the time portion is set to "12:00:00" hours. So irrespective of the date, the calendar date value, and the date value in your transaction tables are never gonna match. 

You should convert the date fields in all the tables to the same format. Either it can be a date format or if you choose to keep it in date/time format, set the time portion to the same hours in all the tables. Either they have to be "00:00:00" or "12:00:00" does not matter. But it has to be consistent.

[deeksha]

Hi @Anonymous 

I changed all the date columns in calendar table, budget and actual tables to date type but still the same problem exists, no data is being displayed.

[Anonymous]

Changing the formatting changes only the way it is displayed. You have to actually discard the time portion (decimal values) from the date value which is internally represented as a decimal number where the integer part is the date and the decimal part is the time where 1 second = 1/(24*60*60)

For example, if you change the format of the date column to a decimal number, you can see decimal places in the date value. 

Please refer to the DAX documentation on date functions and date/time functions.

[deeksha]

@Anonymous 

ohh ok, Will try to work on that.

Thank you.

[deeksha]

@Anonymous 

Thank you soo much for your valuable replies and help.

It worked 🙂