Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Tooley
Frequent Visitor

How do I sum Full Year Total, whilst ignoring Month row context?

How do I create a DAX measure to calculate Full Year Total for the same fiscal year of a month & year in a visual (row context)?

 

A line & clustered column chart will show monthly totals in columns, with the Full Year total essentially a flat horzontal line.

 

Users can filter Fiscal Year from and Fiscal Year to.  The following table illustrates FY Sales expected, based on fiscal years 2022-23 to 2023-24: 

 

Month & YearSalesFY SalesFiscal Year
(Not in visual.  Is a column in Date table)
Apr'221122022-23
May'221122022-23
Jun'221122022-23
Jul'221122022-23
Aug'221122022-23
Sep'221122022-23
Oct'221122022-23
Nov'221122022-23
Dec'221122022-23
Jan'231122022-23
Feb'231122022-23
Mar'231122022-23
Apr'232242023-24
May'232242023-24
Jun'232242023-24
Jul'232242023-24
Aug'232242023-24
Sep'232242023-24
Oct'232242023-24
Nov'232242023-24
Dec'232242023-24
Jan'242242023-24
Feb'242242023-24
Mar'242242023-24

   

I have tried the following DAX however this provides a cumulative total.  I cannot figure out how to sum total for the Fiscal Year:

 

FY Sales =
CALCULATE(
    SUM ( Sales[Value] )
    DATESYTD(
        'Date'[Date],
        "31/03"
    )
)

 

Thanks for any help. 

1 REPLY 1
Greg_Deckler
Super User
Super User

@Tooley You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors