DAX - Calculation of sum for current Fiscal Year

[AnonymeC]

Hello,

I am trying to calculate the weight of the sales per quarter and half fiscal year (considering the whole FY). For that I want to divide the sales of the time period by the total sales of the FY.

To calculate the sales of the time period I just do :

=sum( 'Sales consolidated'[Sales $])

and then use my dates table in the pivot table to show the half and quarter.

The issue is with the total sales of the FY. After research I tried this formula :

=CALCULATE( sum( 'Sales consolidated'[Sales $]), FILTER( Dates, Dates[FY] = "FY23"))

I believed it worked but it doesn't, when I'm using it in my pivot I don't have the value of the whole fiscal year when I'm looking at quarter or half level (I have the correct value for the total though). I woul like to "freeze" this total value so I can use it when looking at quarter/half level to calculate the weight.

Hope it is clear, thanks a lot in advance.

[AnonymeC]

To give you a better idea the pivot table should look like this :

	Column labels
	H1			H2			total (=Full FY)
Row labels	Sales $	Total Sales	Weight	Sales $	Total Sales	Weight	Sales $	Total Sales	Weight
Category A	12000	20000	60%	8000	20000	40%	20000	20000	100%
Category B	25000	50000	50%	25000	50000	50%	50000	50000	100%

[Greg_Deckler · Accepted solution]

@AnonymeC Try:

Measure = CALCULATE( sum( 'Sales consolidated'[Sales $]), FILTER( ALL(Dates), Dates[FY] = "FY23"))

[AnonymeC]

@Greg_Deckler It worked, thanks a lot!!

Have a nice day