compare automaticaly between N and N-1

[Anonymous]

Hello ,

Is it possible in power BI to compare automaticaly between N and N-1 year;

for example I want to obtain 2 curve in the same graph automatically ( one for 2017 and one for 2018 ) 

DATE	SUM
06-oct-18	5
07-oct-18	2
06-oct-17	1
07-oct-17	5

thanks in advance

[Anonymous]

Do you know how to create a dynamic measure for trailing 3 months?   For example, in November, it would calculate Aug, Sept, Oct.....     and in Dec, it would calculate Sept, Oct, Nov.

Thanks!!!

[TomMartens · Accepted solution]

Hey,

yes it's possible to achieve this, but maybe not as simple as you might think 🙂

First I created a dedicated Calendar table (it's always a good idea to have a dedicated Calendar table in your data model) using this DAX statement:

Calendar = 
ADDCOLUMNS(
    CALENDAR("2017-01-01", "2018-12-31")
    ,"Year", YEAR(''[Date])
    ,"Month Day", FORMAT(''[Date],"MM-DD")
)

This is a very simple Calendar table, it just contains a date column, a Year column (this column will be used in the legend field of the line chart) and the "Month Day" (used as x-axis).

Then I created a relationship between both tables like so:

This allows to create a line chart like so:

Hopefully, this is what you are looking for!

Regards,

Tom