Calculate YTD over multiple dimensions

[Anonymous]

Hi All,

I need help with YTD calculation over muliple dimesions. I have Entity, Client, Segment and Product dimesions which are text values.  I tried to calculate Rank however, as these are all text values its not working. 

Entity	Client	Segment	Product	Date	Sales
Google	Elizabeth Johnson	Keystone	Google Analytics	1/28/2020	38.4923
Google	Marco Mehta	Keystone	Google Source	2/6/2020	868.6342
Google	Rob Verhoff	Cornerstone	Google Analytics	4/13/2020	2171.2942
Google	Shannon Carlson	Cornerstone	Google Analytics	4/17/2020	884.7083
Google	Ian Jenkins	Keystone	Google Analytics	4/28/2020	413.1463
Apple	Sydney Bennett	Keystone	Google Source	4/29/2020	486.7066
Apple	Chloe Young	Keystone	Google Analytics	5/8/2020	413.1463
Apple	Wyatt Hill	Keystone	Google Source	5/9/2020	486.7066
Apple	Shannon Wang	Keystone	Google Analytics	5/12/2020	413.1463
Apple	Clarence Rai	Keystone	Google Landscape	5/13/2020	486.7066
Apple	Luke Lal	Healthcare IT	Google Analytics	5/20/2020	1898.0944
Apple	Jordan King	Healthcare IT	Google Analytics	5/27/2020	1265.6195
Apple	Destiny Wilson	Healthcare IT	Google Landscape	5/28/2020	1105.81
Apple	Luke Lal	Healthcare IT	Google Analytics	6/16/2020	1320.6838

[Anonymous]

Hi @Anonymous ,

Try these formulas:

YTD =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[Entity],
            'Table'[Client],
            'Table'[Segment],
            'Table'[Product]
        ),
        YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
            && 'Table'[Date].[Month] = SELECTEDVALUE ( 'Table'[Date].[Month] )
    )
)


LYTD =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[Entity],
            'Table'[Client],
            'Table'[Segment],
            'Table'[Product]
        ),
        YEAR ( 'Table'[Date] )
            = YEAR ( TODAY () ) - 1
            && 'Table'[Date].[Month] = SELECTEDVALUE ( 'Table'[Date].[Month] )
    )
)

Best Regards,

Jay

[Anonymous]

Hi @Anonymous 

Thank you for the response. I tried above DAX formulas however, they are not working correctly. I tried below formula for the current year YTD and it worked fine, however, I am struggling with the following calculations: LY YTD, QTD, LY QTD, MTD and LY MTD.

YTD = CALCULATE(SUM('Sample Data'[Sales]),

FILTER('Sample Data', 'Sample Data'[Client ID]  = EARLIER('Sample Data'[Client ID] ) &&

'Sample Data'[Segment ID]  = EARLIER('Sample Data'[Segment ID] ) &&

'Sample Data'[Product ID]  = EARLIER('Sample Data'[Product ID])&&

'Sample Data'[Location ID]  = EARLIER('Sample Data'[Location ID])&&

'Sample Data'[Index] <= EARLIER('Sample Data'[Index])

)

)

[Anonymous]

Hi Amit,

I have used the above formula, however, it is not providing the correct result. Let me rephrase my requirement, I need to provide Month slicer. Suppose I am filtering on May month it should the YTD for May 22 and also last year' YTD i.e. YTD for May 21. 

[amitchandak]

@Anonymous , YTD is over date and should work across dimensions.  You should a date table to make sure work best

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

or

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4