Forum Discussion

vytautas1410's avatar
vytautas1410
Frequent Visitor
5 years ago

Evolution Index On Matrix Visual

Hello  Everyone, 

I have data of 2018 2019 2020 . I would like to see EVOLUTION INDEX of (2019 and 2020) on matrix visual, using (EINDEX = DIVIDE(1 + [Growth YOY],CALCULATE( 1 + [Growth YOY],ALLSELECTED())) * 100) measure. 

 

 

 

Currently I'm using this measure on Growth YOY:

 
 
 
Growth YOY =
VAR __PREV_YEAR = CALCULATE(SUM('M'[Value]), ALL('Calendar'[Date]) , DATEADD('Calendar'[Date].[Date], -1, YEAR))
VAR __THIS_YEAR = CALCULATE(SUM('M'[Value]),ALL('Calendar'[Date]) ,DATEADD('Calendar'[Date].[Date], 0, YEAR))
    RETURN
IF(__THIS_YEAR, (DIVIDE(SUM('M'[Value]) - __PREV_YEAR, __PREV_YEAR))) 
 
 
 
 
I'm only getting Evolution INDEX on last 12 months , and when I'm filtering by Date, the values are slightly changing.
I believe that the problem is with GROWTH YOY measure .  Could someone help me with these measures ? 
 
Thank you in advance . 

 

 

 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    vytautas1410 Sorry, having trouble following, can you post sample data as text and expected output?


    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    vytautas1410 Indeed, it requires a glance of data model to troubleshoot. Btw, I think in such expressions, ALL('Calendar'[Date]) is redundant.

    VAR __PREV_YEAR = CALCULATE(SUM('M'[Value]), ALL('Calendar'[Date]) , DATEADD('Calendar'[Date].[Date], -1, YEAR))
VAR __THIS_YEAR = CALCULATE(SUM('M'[Value]),ALL('Calendar'[Date]) ,DATEADD('Calendar'[Date].[Date], 0, YEAR))

     When the first parameter of DATEADD func references a date column, it's replaced by CALCULATETABLE ( DISTINCT ( <Dates> ) ); context transition applies and filter context also takes effect accordingly. DATEADD returns a date table after the call, which is surely a subset of ALL(Calendar[Date]).