DAX formula to calculate the full year total for the previous financial year, based on page filter selection

[peejay]

I am trying to calculate Total Revenue for the full financial year (ending 30 Jun) preceding the month selected in a filter using my Data Table's Month End field. I've wasted a lot of time on something which seems (and probably is) straight forward for the experienced DAX user (I'm a relative newbie to DAX).

I have a Revenue table (with Month End date field) related to my tblDataTable on my [Month End] column.
My PBI page has a filter using the tblDateTable[Month End], where the user selects a single month end.

When they select (eg) March 2018, I want to generate a Total Revenue for FY17 (ie Jul16 to Jun17).

My most recent crack at this is as follows:

<PRE>

LastDate:=LASTDATE(tblDateTable[Month End])
StartDate:=IF(MONTH([LastDate])>=7,
EOMONTH([LastDate], -(MONTH([LastDate])+6))+1,
EOMONTH([LastDate], -(MONTH([LastDate])+18))+1
)
EndDate:=IF(MONTH([LastDate])>=7,
EOMONTH([LastDate], -(MONTH([LastDate])-6)),
EOMONTH([LastDate], -(MONTH([LastDate])+6))
)

Rev LY Full Yr:=CALCULATE(
[Revenue],
ALL(tblDateTable[Month End]),
FILTER(
VALUES(tblDateTable[Month End]),
([StartDate] <= [LastDate] && [LastDate] <= [EndDate])
)
)</PRE>

[Revenue] is a measure, being SUM([Net Revenue].
tblDateTable[Month End] is my Month End column in my date table.

I am trying to reset the [Month End] page filter, and replace it with all the months falling between Jul16 to Jun17.

My LastDate gives me the most recent month end value in the filter, and the StartDate gives me the 1st day of the prior financial year and EndDate the last day of the prior financial year.

However, the Rev LY Full Yr measure gives me (blank) for all dates.

I think I'm almost there but I'd be really appreciative if someone could help me solve this and put me out of my DAX misery.

Thanks, PJ

 

[gazpage]

All this stuff is super hard work without a decent calendar table. I don't know to what extent your tblDateTable​ is set up to help, but if it's not I think you should make it so that it is.

For example, the calendar table might have columns like:

Month End
Calendar Year
Calendar Quarter
Fiscal Year
Fiscal Quarter
etc etc

In particular, the Fiscal Year should be based on the 30 June year end. So for all months from July 2016 to June 2017 the Fiscal Year column should just read "2017". July 2015 to June 2016 would just read "2016". You can add a calculated column with some logic to do this if you can't create at the query step.

Then the formula is something like:

Rev LY Full Yr:=
CALCULATE(
   [Revenue],
   FILTER (
      ALL( tblDateTable ),
      tblDateTable[Fiscal Year] = MAX ( tbleDateTable[Fiscal Year] ) -1
   )
)

 

[gazpage]

Or, if you really just want to solve this particular issue...

Rev LY Full Yr:=
CALCULATE(
   [Revenue],
   PREVIOUSYEAR ( tblDateTable[Month End]), "30/06/2017" )
)

 

[peejay]

Thanks Gazpage

This has done the job - MANY thanks for your help.
I already had an expansive date table and had tried using my Fiscal Year field but got nowhere - most likely due to my inexperience with the FILTER command.

But thanks to your help I can now move on with my Power BI life