Tricky YTD calculation with Fiscal Year

[cfugge]

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?

Many thanks in advance.

C. 

[cfugge · Accepted solution]

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.

[PabloDeheza]

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!