cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Responsive Resident

## Measure for Fiscal Year Totals

See above for a Date table I built within my report.  I am trying to do a measure to calculate total units by fiscal year 2022 and 2021.
This measure works but in doing so I would be required to edit the report yearly.

CY Units = CALCULATE(SUM(SalesSummary[Units]),FILTER('Date','Date'[Fiscal Year]=2022))

Any idea how to make this dynamic?  Tried this measure but it returned the total for ALL fiscal years not just 2022.

CY Units = CALCULATE(SUM(SalesSummary[Units]),FILTER('Date',MAX('Date'[Fiscal Year])))

1 ACCEPTED SOLUTION
Responsive Resident

Thanks for replies.
I think I just got this to work on my own. See below for CY and PY measures. I didnt have to create another table either.

CY Units = CALCULATE(SUM(SalesSummary[Units]),FILTER('Date','Date'[Fiscal Year]=MAX('Date'[Fiscal Year])))
PY Units = CALCULATE(SUM(SalesSummary[Units]),FILTER('Date','Date'[Fiscal Year]=MAX('Date'[Fiscal Year])-1))

4 REPLIES 4
Responsive Resident

Thanks for replies.
I think I just got this to work on my own. See below for CY and PY measures. I didnt have to create another table either.

CY Units = CALCULATE(SUM(SalesSummary[Units]),FILTER('Date','Date'[Fiscal Year]=MAX('Date'[Fiscal Year])))
PY Units = CALCULATE(SUM(SalesSummary[Units]),FILTER('Date','Date'[Fiscal Year]=MAX('Date'[Fiscal Year])-1))

Super User

Hi @IamTDR ,

Does it work with this measure?

```CY Units =
VAR _maxYear = MAX ('Date'[Fiscal Year] )
RETURN
CALCULATE (
SUM ( SalesSummary[Units] ),
FILTER ( 'Date',
'Date' [Fiscal Year] = _maxYear
)
) ```

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Responsive Resident

Yup. Seems this measure works as well. Thanks

Super User

Hi @IamTDR

Create New Table = DISTINCT('Calender'[Fiscal Year])
Make relationship witn your calender table Fiscal Year to Fiscal Year
Now do measure
= VAR _FY = SELECTEDVALUE('New Table'[Fiscal Year])
RETURN
CALCULATE(SUM(SalesSummary[Units]),_FY)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors