DAX YTD Growth Rate % of an Index (non cummulative)

[McShock]

Hi, can you experts please help me with this dax measure. Please note that I am new to Dax.

I am trying to calculate the YTD Growth Rate in % of an Index. I've watched a bunch of YT Videos but they all explain how to calculate YTD cummulative for revenues, sales etc. This is not what I need. What I need is calculating the difference between the level of an index at max date (latest date in the table) compared to the level of the index at 31.12 the year before. And I only need this to be done for the current year. The previous years are already completed. There is no YTD. I join the screenshot of the table that I have created and also a screenshot of Excel with the result I want to achieve. My database is on a monthly basis and the latest date is 31.3.2024. Therefore I should get YTD for Jan 2024, Feb 2024 and Mar 2024.

I would really appreciate your help.

Thank you in advance

Markus

[v-yangliu-msft · Accepted solution]

Hi  @McShock ,

You can change ALL() to Allselect():

Refer to：

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Measure =
var _today=TODAY()
var _maxdate=
DATE(YEAR(_today),1,1)-1
var _mindate=
DATE(YEAR(_maxdate),MONTH(_maxdate),1)
var _sumlastyear=
SUMX(
    FILTER(ALLSELECTED('IndicesPrices'),
    'IndicesPrices'[Date]>=_mindate&&'IndicesPrices'[Date]<=_maxdate),[IndexTR])
var _sumcurrentyear=
SUMX(
    FILTER(ALLSELECTED('IndicesPrices'),  YEAR('IndicesPrices'[Date])=YEAR(MAX('IndicesPrices'[Date]))&&MONTH('IndicesPrices'[Date])=MONTH(MAX('IndicesPrices'[Date]))),[IndexTR])
return
IF(
    YEAR(MAX('IndicesPrices'[Date]))=YEAR(_today),
DIVIDE(
    _sumcurrentyear-_sumlastyear,_sumlastyear))

Best Regards,

Liu Yang

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

[McShock]

Thank you very much Liu Yang!!!

Have a nice day!

Markus

[v-yangliu-msft]

Hi  @McShock ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _today=TODAY()
var _maxdate=
DATE(YEAR(_today),1,1)-1
var _mindate=
DATE(YEAR(_maxdate),MONTH(_maxdate),1)
var _sumlastyear=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate),[IndexTR])
var _sumcurrentyear=
SUMX(
    FILTER(ALL('Table'),    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[IndexTR])
return
IF(
    YEAR(MAX('Table'[Date]))=YEAR(_today),
DIVIDE(
    _sumcurrentyear-_sumlastyear,_sumlastyear))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[McShock]

Hi Liu

This was very kind of you having taken the time to reply in such a detailed way, even including the file with the solution. I am really greatful for this. Still, I am not able to replicate on my side. I copied the dax and made the adjustments that I think I had to do but I am getting very strange results. It must be frustrating for you to deal with beginners but if you could have a look on the attached power bi file that would be great. Have a nice day.

Markus

file