topic Re: Measure for fiscal year budget total - ignoring month in line chart? in DAX Commands and Tips

Measure for fiscal year budget total - ignoring month in line chart?

SaAn — Tue, 10 Oct 2023 12:12:16 GMT

Hi,

I'm struggeling creating a measure, which will display the fiscal year total sum all months (Budget values) to compare with the Actual MAT (moving annual total). I expect a straight line for each fiscal year period in the chart. I somehow need to ignore the month filter context or the like - but how? I have tried to illustrate the expected result in the picture below.

The simplified data model for this case is one Date dimension and two facts holding actual and budget values, respectively.

The three main measures in this case are:

Budget Value = SUM(Budget Value)

Actual Value = SUM(Actual Value)

Actual MAT =

VAR MAT_StartDate = EDATE( MAX('Date'[Date]), -12)+1

VAR MAT_EndDate = MAX('Date'[Date])

VAR Result = CALCULATE( [Actual Value], 'Date'[Date] >= MAT_StartDate && 'Date'[Date] <= MAT_EndDate)

RETURN Result

Thanks in advance!

Re: Measure for fiscal year budget total - ignoring month in line chart?

DallasBaba — Tue, 10 Oct 2023 18:15:37 GMT

Hi SaAn

You can modify your measure as follows:

Actual MAT =
VAR MAT_StartDate = EDATE( MAX('Date'[Date]), -12)+1
VAR MAT_EndDate = MAX('Date'[Date])

VAR Result = CALCULATE( [Actual Value], ALL('Date'), 'Date'[Date] >= MAT_StartDate && 'Date'[Date] <= MAT_EndDate)

RETURN
Result

=========== Or Another approach is to ========

Create a Measure for Budget Totala = SUM('Budget'[Budget Value])

Create a Measure for Actual Total = SUM('Actual'[Actual Value])

Create a Measure for Fiscal Year MAT Total =
VAR MAT_StartDate = EDATE(MAX('Date'[Date]), -12) + 1
VAR MAT_EndDate = MAX('Date'[Date])
VAR FiscalYearStart = DATE(YEAR(MAT_StartDate), 1, 1)
VAR FiscalYearEnd = DATE(YEAR(MAT_StartDate), 12, 31)
VAR Result =
CALCULATE(
[Actual Total],
ALL('Date'),
FILTER('Date', 'Date'[Date] >= FiscalYearStart && 'Date'[Date] <= FiscalYearEnd)
)
RETURN
Result

Please let me know is this work.

Thanks

Babatunde Dallas

Re: Measure for fiscal year budget total - ignoring month in line chart?

SaAn — Wed, 11 Oct 2023 09:06:45 GMT

Hi DallasBaba

Thanks a lot for the response.

I have tried to replicate the measure you suggested. I have also created a modified version using Budget Total instead of Actual Total as the aim is to have a measure depicting the Fiscal Year budget total (not MAT) as illustrated in my attachment in the original post.

In addition, our Fiscal Year is running from May to April. I have also tried to modify the measure according to that.

The result by replicating your suggested measures looks like this:

I think the key aspect is to be able to sum budget by Fiscal Year, while ignoring the month context in the line chart. But I don't know how to do that in DAX.

Re: Measure for fiscal year budget total - ignoring month in line chart?

DallasBaba — Wed, 11 Oct 2023 17:37:52 GMT

SaAn you can modify the measure by defining the start and end dates of the Fiscal Year based on the maximum date in your Date dimension and then use CALCULATE to sum the Budget Value for that period.

Fiscal Year Budget Total =
VAR FiscalYearStart = DATE(YEAR(MAX('Date'[Date])), 5, 1)
VAR FiscalYearEnd = DATE(YEAR(MAX('Date'[Date])) + 1, 4, 30) // Assuming your Fiscal Year ends on April 30th
VAR Result = CALCULATE([Budget Value], ALL('Date'), 'Date'[Date] >= FiscalYearStart && 'Date'[Date] <= FiscalYearEnd)
RETURN Result

Note:
This measure calculates the Budget Value for the specified Fiscal Year (from May to April) by ignoring the month context.
It first

Please let me know is this works
Thanks

Re: Measure for fiscal year budget total - ignoring month in line chart?

SaAn — Thu, 12 Oct 2023 14:28:07 GMT

DallasBaba Brilliant. It worked. Thanks a lot!

Just to be sure, it's the ALL('Date') subset of the CALCULATE that dictates to ignore the month context first of all?

Re: Measure for fiscal year budget total - ignoring month in line chart?

DallasBaba — Thu, 12 Oct 2023 14:45:06 GMT

SaAn Yes, you are correct.

The ALL('Date') part of the CALCULATE function ensures that the month context is ignored when calculating the Fiscal Year Budget Total. It removes any filters or context applied to the 'Date' dimension, which allows you to calculate the Budget Value for the specified Fiscal Year (from May to April) without considering the specific month filter.

This is an essential part of the measure to achieve the desired result.

I'm glad to hear that it worked for you! Please like the reply.

Thanks

Re: Measure for fiscal year budget total - ignoring month in line chart?

SaAn — Fri, 13 Oct 2023 06:40:48 GMT

Thanks for your help DallasBaba .