Filter current financial year from measure

[Anonymous]

Hi All

Hoping someone can help me out here.

As part of a report I have a measure that calculates the ratio between contract value and funding we have approved. As there is some lag in reporting we need to exclude the current financial year when calculating this ratio. I've got the following measure:

Leverage Ratio =

CALCULATE(DIVIDE(SUM('Grants Data'[Total Value of Major Contracts Reported]), SUM('Grants Data'[Funds Approved (Exc. GST)]), 0), FILTER(ALL('Calendar'), 'Calendar'[FinYearNumber] < MAX('Calendar'[FinYearNumber])))

This works fine in terms of a static reporting point. If someone wants to know what the current leverage ratio is then I have that for them. However, if a user selects a previous financial year on the slicer I have on my report page then they will see the ratio value for the financial year prior to the financial year they have selected which is a lot less helpful.

Essentially it would be good to be able to exclude only the current financial year (ie. 2020/21 at the moment) from the measure but if the selected financial year was prior to this current financial year (for example, 2018/19) they would see the ratio value for that financial year rather than that financial year minus 1.

I assume I need to do something to check if the financial year selected is the MAX or not (maybe a variable in the measure) but really not sure how to go about using that once I have the variable (or even if a variable would be the best way of doing things).

Thanks for any help anyone can provide.

[Greg_Deckler]

@Anonymous - Try this:

Leverage Ratio =
  VAR __MaxFinYear = MAXX(ALL('Calendar'),[FinYearNumber])
RETURN
  CALCULATE(
    DIVIDE(
      SUM('Grants Data'[Total Value of Major Contracts Reported]), 
      SUM('Grants Data'[Funds Approved (Exc. GST)]), 
      0
    ), 
    FILTER(ALL('Calendar'), 'Calendar'[FinYearNumber] < __MaxFinYear)
  )

Assuming that your calendar only has up through the current financial year this should work.

[Anonymous]

Thanks for that. Apologies in the delay for getting back to you.

This doesn't seem to work how I was expecting.

This is good in that it will only show the leverage ratio for the data up to and including the previous financial year. So it's now essentially a static data point that will change when we move into the next financial year (which is not a bad thing).

However, what I was hoping for was that when I slice to a prior financial year (say 2014/15) it will show me the leverage ratio for the data up to that financial year and only if I have sliced to the current financial year (2020/21) or have no slicer value will it ignore data for the current financial year. Does that make sense?

My calendar table has two financial year fields: FinYearNumber which calculates the financial year as a year (ie. 2020), and FinYearDisplay which displays the financial year as something like 2020/2021. I use FinYearDisplay in my slicers, so I'm not sure if that is having an effect here.

Thanks

[Anonymous · Accepted solution]

Managed to get the following to work. I'll admit it isn't the cleanest way of achieving it but it works for what I'm trying.

Leverage Ratio v3 = 
  VAR __MaxFinYear = MAXX(ALL('Calendar'),[FinYearNumber])
  VAR __SelectedFinYear = SELECTEDVALUE('Calendar'[FinYearNumber])
RETURN
IF(__SelectedFinYear = __MaxFinYear, 
  CALCULATE(
    DIVIDE(
      SUM('Grants Data'[Total Value of Major Contracts Reported]), 
      SUM('Grants Data'[Funds Approved (Exc. GST)]), 
      0
    ), 
    FILTER(ALL('Calendar'), 'Calendar'[FinYearNumber] < __MaxFinYear)
  ),
IF(SELECTEDVALUE('Calendar'[FinYearNumber]) = BLANK(),
CALCULATE(
    DIVIDE(
      SUM('Grants Data'[Total Value of Major Contracts Reported]), 
      SUM('Grants Data'[Funds Approved (Exc. GST)]), 
      0
    ), 
    FILTER(ALL('Calendar'), 'Calendar'[FinYearNumber] < __MaxFinYear)),
DIVIDE(SUM('Grants Data'[Total Value of Major Contracts Reported]), 
      SUM('Grants Data'[Funds Approved (Exc. GST)]), 
      0)))

[Greg_Deckler]

@Anonymous - Did you get your issue resolved?

[amitchandak]

@Anonymous , Not very cleat but you can use time intelligence with a date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"5/31")) // End year of choice 
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"5/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"5/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"5/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"5/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below, Here year is FY Year

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

FY Year of choice -https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.