cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shelley Member
Member

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

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?
 
1 ACCEPTED SOLUTION

Accepted Solutions
Shelley Member
Member

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

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!
6 REPLIES 6

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

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.

2019-02-15 01_22_29-Time Intelligence Demo - Power BI Desktop.png

 

 

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

DatesMTD = CALCULATE(SUM(Sales[SalesAmount]),DATESMTD('Calendar'[DateKey]) )
 
Datesmtd.JPG
 
Thanks,
Amit
Shelley Member
Member

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

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?

Community Support Team
Community Support Team

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Shelley Member
Member

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

@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:
Capture.PNG
 
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 Member
Member

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

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!
Highlighted
Community Support Team
Community Support Team

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

hi, @Shelley 

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

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.