cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.

Markus

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

Thank you very much Liu Yang!!!

Have a nice day!

Markus

Community Support

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

Frequent Visitor

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