How to filter on a month, calculating properly, using DATESYTD Function?

[Shelley]

Hi All, I appreciate the help.

I'm using this YTD function:

YTD Total Order $ Converted =

CALCULATE ([Total Order $ Converted],

DATESYTD ('RA_Daily_Calendar'[Date],"09/30"))

It works as expected; hoever when I select a month within the YTD range, thinking I will see the total for that month, it shows YTD THRU that month. Is there another filter or something I can change to enact this behavior? That is, when I select a month, within the YTD range, it shows the total for that month?

[Anonymous]

HI @Shelley,

You can modify your DAX expression as per below screenshot.

You can select any month from Slicer, the table visual will show total till that month only.

Or, may be you are looking for DATESMTD function. You can use 

DatesMTD = CALCULATE(SUM(Sales[SalesAmount]),DATESMTD('Calendar'[DateKey]) )

Thanks,
Amit

[Shelley]

Thank you for the help, but I don't see how the formula you propose is different than mine? Also, it does the same as mine, it calculates YTD THRU the month selected, so if I have Dec selected, it calcs Oct, Nov and Dec; however, if Dec is selected, I only want Dec total to show. Likewise, if I have Dec and Jan selected, I want to see the two months added together - I don't want the full year to date value. Is there any way to do this?

[v-lili6-msft]

HI, @Shelley

DATESYTD ( 'Date'[Date] ) Function corresponds to a filter over the date column using FILTER called by CALCULATETABLE, such as in the following code:

CALCULATETABLE (
    FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
                YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ),
                
            )
        )
    )
)

So it will lead to your case, for your requirement, you need to try this way:

Whether there is a fiscal year column in your date table?

YTD Total Order $ Converted =

CALCULATE ([Total Order $ Converted],

DATESYTD ('RA_Daily_Calendar'[Date],"09/30"))

Your fiscal year should from Octoberof this year to September of next year

So please add a fiscal year column by these formulas

Year = YEAR(RA_Daily_Calendar[Date]) 

Month = MONTH(RA_Daily_Calendar[Date]) 

fiscal year = IF(RA_Daily_Calendar[Month] IN{1,2,3,4,5,6,7,8,9},RA_Daily_Calendar[Year]-1,RA_Daily_Calendar[Year] )

Then use this formula to add a ytd measure

YTD Total Order $ Converted =
CALCULATE (
    [Total Order $ Converted],
    FILTER (
        ALLSELECTED ( 'RA_Daily_Calendar' ),
        AND (
            'RA_Daily_Calendar'[Date] <= MAX ( 'RA_Daily_Calendar'[Date] ),
            RA_Daily_Calendar[fiscal year] = MAX ( RA_Daily_Calendar[fiscal year] )
        )
    )
)

Best Regards,
Lin

[Shelley]

@v-lili6-msft Thank you for the very thorough response. This is so close. We do have our fiscal year in our calendar. So this is the formula I've used as you suggested. 

YTD Total Order $ Converted =

CALCULATE ([Total Order $ Converted],

FILTER (

ALLSELECTED ( 'RA_Daily_Calendar' ),

AND (

'RA_Daily_Calendar'[Date] <= MAX ( 'RA_Daily_Calendar'[Date] )

&& YEAR ( 'RA_Daily_Calendar'[Date] ) = YEAR ( MAX ( 'RA_Daily_Calendar'[Date] ) ),

RA_Daily_Calendar[Fiscal_Year] = MAX ( RA_Daily_Calendar[Fiscal_Year] )

)

)

)

Rather than the fiscal YTD total (sum of Oct, Nov, Dec and Jan), this new formula is resulting in the current fiscal month's total (Jan only).

I have these slicers at the top of the page:

What I want to happen is show YTD total orders by default and then if a user selects a fiscal quarter or a month(s), then have this same expression show the total according to those filters. 

With your formula as written above, as I mentioned it defaults to showing only Jan (the latest month's data), and when I select a quarter or month(s), it does calculate properly, but it's more user-friendly if the users don't have to make any selections to see current YTD. This is so close to working the way I'd like it to work. Is there a way to show YTD by default, and still allow the functionality of this formula?

Another potential idea - see the slicer on the left in my image - it "activates" YTD comparison for prior years. When this is not set to "1" (i.e. it is 0->1) it shows the user the full year's data for the last two years and the current year to date (I have 3 different columns, 3 different expressions -> prior prior YTD, prior YTD, current YTD). When this slicer is set to 1, it shows all three years at YTD comparison. I did this by putting a flag on all the dates in the date table -> 1 for within the current YTD, 0 if not.  Is there a way to use whether this slicer is set to calculate the YTD two different ways? One that's YTD, and the other that allows selection of random quarters or months?? 

Thank you for your help!

[Shelley · Accepted solution]

I think I may have gotten this figured out thanks to @v-lili6-msft ! I modified your formula to this:

YTD Total Order $ Converted =

    CALCULATE ([Total Order $ Converted],

    FILTER (

        ALLSELECTED ( 'RA_Daily_Calendar' ),

        AND (

            'RA_Daily_Calendar'[Date] <= MAX ( 'RA_Daily_Calendar'[Date] ),

            RA_Daily_Calendar[Fiscal_Year] = MAX ( RA_Daily_Calendar[Fiscal_Year] )

        )

    )

)

I removed the calendar year reference. All my preliminary testing seems to work as I'd expect. THANK YOU SO MUCH!

[v-lili6-msft]

hi, @Shelley 

It's pleasant that your problem has been solved, could you please mark my reply as Answered too?

Best Regards,

Lin