Skip to main content
cancel
Showing results for 
Search instead 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

Reply
McShock
Frequent Visitor

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

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

Dax YTD Problem.pngExcel Solution.png

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
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))

vyangliumsft_0-1712909166895.png

 

 

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.

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
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))

vyangliumsft_0-1712909166895.png

 

 

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.

Thank you very much Liu Yang!!!

Have a nice day!

Markus

v-yangliu-msft
Community Support
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:

vyangliumsft_0-1712730473421.png

 

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

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.

MarkusResultsDaxGrowthMSwrong.png

 

file

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors