Total of measure returning only last year.

[BryanSt]

I have been going around in cirlces on this one.

I have created a measure that works great when used by financial year or some other time basis, but the totals are returning the last year total or a (Blank), depending on the table I am retriving the values.

My measure looks at a date for working out is if it an actual amount (one table) or a forward budget (another table).  I have a chart of accounts in a another table, which links via the lowest level account code.  My Chart of Accounts has a number of groupings for a range of individual account codes (5 acct codes are classed as "Taxes") as well if an account code is a leaf (no a parent code, which is a measure).  I am using that to calculate the cost.

Here is the measure code for Payments to Govt for looking at the Budget Data.  I have omitted the other date based code as it works across all my other measures.

abs(CALCULATE(Budget_data[YTD Forward Est],'CoA_Full'[Acct_Type Level 1] IN { "Taxes" },'CoA_Full'[IsLeaf] IN { TRUE }))

When I use that in a table I get this result. 

Have done lots of searches on the web & Community, but nothing that I can utilse in my situtation.   I have looked into AllSelected and  HASONEFILTER, but no joy.

I am using the same Measure for chart data by year and a Tile for a total sum, based on a Date Slicer.  Chart is fine, as they are by Date, but tile will only work when there is a single year selected.  

Help greatly appreciated.

Thanks

[OwenAuger]

Hello,

At a glance, it looks like the measure [YTD Forward Est] modifies the date filter context to a YTD period - is that correct?

If so, that would explain why at the total level, you get the same result as the latest year (2021-22).

My suggestion would be that you need to rewrite [YTD Forward Est] in such a way that is sums over your date table using SUMX, at an appropriate granularity (e.g. month or year).

Could you post the code for that measure and some more detail on the tables in the data model, and I could come up with more specific suggestions.

Regards,

Owen

[BryanSt]

Owen

Thanks for the reply. 

I am using the following code for my YTD figures, as we are using July - June as financial years, so we cant use the inbuilt time-intelligence.  It would be nice if PowerBi recognised other FY schemes.

YTD Forward Est =
CALCULATE(sum('Budget_data'[Budget Forward Est]),
DATESYTD('Date'[Date],"06-30"),
ALL('Date'))

The code is basically the same for my actuals table, just pointing to a different table and measure.

My Date table has the all the quarters (as we only get quarterly data) loaded from an Excel file that already calculates the Financial year based on the month number and year.  These are loaded as columns covering all dates from the first actual (2011) to last budget (end of FY 2021-22).  I use the Date column (dd/mm/yyyy) to create a month name and number measure in the date table for other calculations and visuals.

My actual and budget data tables both have a loaded date, account code and a value for each applicable quarter as columns.  I use a measure to get the correct signage from my related chart of accounts table and apply that to the loaded value. All data that I am sourcing has postive numbers, but I need to have use the chart of accounts signage. 

My Date table is related to both the Actuals and Budget tables by the Date and the Chart of Accounts is linked via the Account Code.

Hope that helps

Initially I tried to use the inbuilt functions (as in a quick measure) with no date specified, but the values returned were not financial year to date, so I have been using the above code to calculate financial YTD (July to June).

[BryanSt · Accepted solution]

In the end, I looked at Owen's advice and had to create a new measure that was not using the YTD measures, as they were returning the worng/incorrect total.

I created a new measure that looked at the quarterly results and wrapped them in a SUMX. 

CALCULATE(sumx(Budget_data,CALCULATE('Budget_data'[Forward Est],'CoA_Full'[Acct_Type Level 1] IN { "Taxes" }, 'CoA_Full'[IsLeaf] IN { TRUE })))

Once I got that to work the totals were as expected.