YTD Sales Excluding Current Month

DAX DAX Calculations
1

Hi Team, request one help from you. I need to calculate YTD Sales excluding current month for example I am sitting in Feb month and need to have YTD Jan sales. Measure should be dynamic so that when I go in March month then i should get YTD Sales till February month. My company’s fiscal year is from Jul-Jun.

Any link or videos will do. Thanks in advance.

Regards
Harish Rathore

2

@harishrathore,

Give this a go:

YTD w/o Curr Mo = 

VAR _CurrYrMo = 
CALCULATE(
    MAX( Dates[MonthnYear] ),
    Dates[Date] = TODAY()
)

VAR _Result =
CALCULATE(
    TOTALYTD(
        [Total Sales],
        Dates[Date],
        ALL( Dates),
        "6/30"
    ),
    FILTER(
        ALL(Dates),
        Dates[MonthnYear] < _CurrYrMo
    )
)

RETURN
_Result

Shown below w/comparison to sliced table to confirm same result:

image

I hope this is helpful. Full solution file included below.

3 Likes
3

@BrianJ - Thanks a ton Brian… Brilliant solution… :smiling_face_with_three_hearts:

Regards
Harish Rathore

4

@harishrathore,

Great - glad to hear that worked well for you. It was interesting for me as well, since I almost never use the “canned” TI functions like YTD, FIRSTDATE, etc., so it was interestiing to explore them here. Nothing inherently wrong with them, I just find it tiring to keep track of them all. With MIN, MAX, DATEADD, DATESBETWEEN and DATEDIFF, you can “roll your own” for any TI function you might need.

  • Brian
1 Like