cancel
Showing results for
Did you mean:  Helper I

## Tricky YTD calculation with Fiscal Year

Hi all,

apologies if this has been treated already.

I have to manage a simple Sales YTD calculation for a FY starting Dec 1 and ending Nov 30. I have a calendar table with all needed field but for multiple reasons I cannot use the time intelligence functions in all report. I have started therefore to build the following YTD measure:

NSV_YTD_A =
VAR __Date = MAX('Fact_Actuals'[Date_comp])
VAR __StartOf = IF( MONTH(__Date)<12,DATE(YEAR(__Date)-1,12,1), DATE(YEAR(__Date),12,1))
VAR __DATESYTD = FILTER(SELECTCOLUMNS(ALL('Fact_Actuals'),"Date_comp",[Date_comp]),[Date_comp] >= __StartOf && [Date_comp] <= __Date)
RETURN
SUMX(FILTER(ALL('Fact_Actuals'),[Date_comp] IN __DATESYTD),Fact_Actuals[NSV])

In my assumption this should give the YTD NSV but unfortunately doesnt as is releasing all other filters made via report slicers (e.g. IsIntco below): How can I amend the measure above to maintain all other filters and calculate correctly YTD?

C.
1 ACCEPTED SOLUTION  Helper I

Hi @PabloDeheza, thanks for this. I did try with VALUES but unfortunately it did not work. After multiple reads I solved as follows via a quite simple CALCULATE with REMOVEFILTERS:

``````NSV_YTD_A =
VAR __Date = MAX('Fact_Actuals'[Date_comp])
VAR __StartOf = IF( MONTH(__Date)<12,DATE(YEAR(__Date)-1,12,1), DATE(YEAR(__Date),12,1))
VAR __DATESYTD = FILTER(SELECTCOLUMNS(ALL('Fact_Actuals'),"Date_comp",[Date_comp]),[Date_comp] >= __StartOf && [Date_comp] <= __Date)
VAR __MyTD = if(countrows(Fact_Actuals)>0,
CALCULATE(
sum(Fact_Actuals[NSV]),
REMOVEFILTERS ( Dim_Dates[FY_Period] ),
Fact_Actuals[Date_comp] IN __DATESYTD
))
RETURN
__MyTD``````

C.

2 REPLIES 2  Helper I

Hi @PabloDeheza, thanks for this. I did try with VALUES but unfortunately it did not work. After multiple reads I solved as follows via a quite simple CALCULATE with REMOVEFILTERS:

``````NSV_YTD_A =
VAR __Date = MAX('Fact_Actuals'[Date_comp])
VAR __StartOf = IF( MONTH(__Date)<12,DATE(YEAR(__Date)-1,12,1), DATE(YEAR(__Date),12,1))
VAR __DATESYTD = FILTER(SELECTCOLUMNS(ALL('Fact_Actuals'),"Date_comp",[Date_comp]),[Date_comp] >= __StartOf && [Date_comp] <= __Date)
VAR __MyTD = if(countrows(Fact_Actuals)>0,
CALCULATE(
sum(Fact_Actuals[NSV]),
REMOVEFILTERS ( Dim_Dates[FY_Period] ),
Fact_Actuals[Date_comp] IN __DATESYTD
))
RETURN
__MyTD``````

C.  Super User

Hi @cfugge !
To allow the measure to get filtered, then you need to use another function instead of ALL(), you can try using VALUES instead.

Let me know if that helps!  