Skip to main content
cancel
Showing results for 
Search instead 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

Reply
IamTDR
Responsive Resident
Responsive Resident

Measure for Fiscal Year Totals

Image.png


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])))

Thanks in advance!

 
1 ACCEPTED SOLUTION
IamTDR
Responsive Resident
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))

View solution in original post

4 REPLIES 4
IamTDR
Responsive Resident
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))

tackytechtom
Super User
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
    )
) 

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Yup. Seems this measure works as well. Thanks 

PijushRoy
Super User
Super User

Hi @IamTDR 

 

Please try this
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)

 

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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