% Difference from the previous quarter

[daniellamoore29]

Please can someone help me!!

I'm trying to work out the % difference from the previous quarter filtering through the same indicator name and FY. 

This is my table visual:

This is my data model showing relatiomships:

I have 3 measures to work out the total sum of this quarter and last quarter, and then work out the % difference, however my measure is not recognising the calendar table. 

FYI - the Calendar table was created using calculated columns i..e, month, FY etc, related to the 'Period' column in the Target table which is a DATE data type. So this is the link between the Calendar and Target table. 

Measures I want to use: 

1. This Qtr = CALCULATE(sum('Report'[Metric Value]),

FILTER('Calendar','Calendar'[Quarter]=max('Calendar'[Quarter])))

2. Last Qtr= (CALCULATE(sum('Report'[Metric Value]),

FILTER(ALLEXCEPT('Indicator', Indicator[Indicator Name]),

'calendar'[Quarter]=MAX('Calendar'[Quarter])-1)))+0)

Error message returned: A single value for column 'Quarter' in table 'calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

3. %Difference = divide(([This Qtr]-[Last Qtr]),[Last Qtr])

What am I doing wrong or what can I do please?

[vanessafvg]

there are several ways to solve this issue, and without knowing everything you want to solve in this model its hard too know the best way, but i think to simplify this issue so that you dont have to change your date relationship, because you already have a one to one relationship with target and report.

So the solution is it might be easiest to just merge these two tables, ie  target table and report table in power query on the target id, assuming that that is a unique relationship and enforces the one 1 one and you will only have a one row per target and report table row.  

so your date table relationship should be one to many to the new table  linking on period

you also can then use your indicator values against target too

That in theory should solve your issue.  so instead of having 2 tables  you will combine them into 1. and that will link directly to date hopefully solving your issue, otherwise feel free to share your model with me so we can get to the bottom of the issue

[daniellamoore29]

Hi @Van , sorry If I'm not explaining this clearly - I'm a beginner with Power BI. So,

1. The target table is where a user records what the monthly, quarterly or yearly target is against a metric name i.e., for metric name "No. of schools opened" there are 4 records (rows) which shows the quarterly targets for Q1, Q2, Q3 & Q4. This is calculated through the 'Period' column which is a date data type. 

2. The report table is where users go and report on their quarterly targets i.e., for metric name "No. of schools opened" the target for Q1 was 10, they have achieved 12 etc. The report and target table have a 1:1 relationship because one report can only have one target, and one target can only have one report against it. 

3. The calendar table was created to understand what the finanial year, quarter and month was from the 'Period' column in the target table. The target and calendar table have a Many to one relationship because a target can only have one date, however a date can have many targets.

You mention you would link the below, however the date column is in the target table because thats where the quarterly, monthly or yearly targets are set.

• calendar should be connect to report separately
• calendar should be connected to target separately  

If I do need to do what you've suggested, how do I do this please? Do I need to add the 'Period'' (date) column in the report table?

[vanessafvg]

out of interest why is your report not linked directly to your date table?

[daniellamoore29]

Hi @vanessafvg , The 'period' column in the Report able is a date data type. I wanted to extract the financial year, financial quarter and financial month, so I created another table in Power BI called Calendar. I think directly linked the 'period' data column to the date in the calendar table.

[vanessafvg]

the problem is (and its difficult to tell without access to your model) but to me it looks like its going via the target table, it should be directly linked, your report table should be directly linked to date in a one to many relationship to your calendar

ie active relationship one to many from calendar to report,  I can't see the relationship in your model pic above.

otherwise please supply your model so one can see what is going on.  

[daniellamoore29]

@vanessafvg this is my full data model. The targetID is connected to the Report table, so the calendar target and report tables should all be linked  no?

[vanessafvg]

This is actually difficult to help you with such limited information.   

What is the meaning of linking target to report?   I will need to understand the business objective of what you are trying to do here.     

The issue here and the error you are getting  is that it is unable to return a single value for your measure which means your relationships between your tables are returning multiple rows rather than just one value, which in the case of a measure, it requires one value.

This problem almost always lies in how the data is modelled.

You will need to explain what you are actually trying to do, i think more business context is required here.

I personally would never link it that way;

calendar should be connect to report separately

calendar should be connected to target separately  

but without understanding  what you are trying to measure, and why target sits in the middle the technical only information only you have provided isn't enough to help you solve this problem.

hope that makes sense.