Forum Discussion

Tulio_DL's avatar
Tulio_DL
Icon for Helper I rankHelper I
9 years ago
Solved

Year over year graphic

Hi everyone!

 

I've been annalizing a table (not direct query, but dynamic) and need to show the year over year behavior of a data in the example as follows. I want to show, in graphic, the comparison of the sum of the column "value" of a period of time (year or month) to the sum of the same period of the year or month before. Consider I will be filtering my report as "B" at column "Filter 2" and "C" at column "Filter 3", also I already have a calendar table connected to it.

 

 

 

Graph A is a very simple one and shows how the value data behaves over time which is fairly easy to put out,

 

 

 

 

The analisis I'm trying to show is the Graphic B and C as follows, keeping in mind that my query is dinamyc (not direct).

 

 

 

In time, is there a way to show the compared results as % instead of the raw value?

 

 

Thanks in advance!

Diego

 

 

 

 

 

 

 

 

Need Help

4 Replies

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    First of all, what do you mean by your query is dynamic and not Direct Query?  All queries in Power Query are "dynamic" with the difference being whether or not you import data into the data model or leave it at the source.

     

    Aside from that, in trying to summarize your question, you want to show YoY or MoM differences?  You will need to create a measure(s) using DAX.  This is a well documented concept (i.e. "Time Intelligence").  Something like YTD or Same Period Last Year have built in functions for this; if you have a custom calendar or other filter arguments you may need to create your own measure, but again, this is well documented online.

     

    Lastly, as for showing the value as a percentage, once you have your YTD (or similar) measure, you would divide it by the previous year's YTD measure and subtract 1, then format as a percentage.  You may need to wrap your measure in a FORMAT() function, or use the GUI to change the format of the measure.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Tulio_DL,

     

    You can try to use below formula if it suitable for your requirement:

     

    Measures:

     

    Current Year Total=
var currDate=MAX(Table[Date])
return
SUMX(FILTER(ALLSelected(Table),Year([Date])=Year(currDate)),[Value])

Previous Year Total=
var currDate=MAX(Table[Date])
return
SUMX(FILTER(ALLSelected(Table),Year([Date])=Year(currDate)-1),[Value])

Growth = [Current Year Total] - [Previous Year Total]

Growth %=[Growth]/ [Previous Year Total]

     

    Regards,

    Xiaoxin Sheng