Date Slicer to show 2 months revenue

[CTan42]

I have a 12months revenue table with different regions. Is it possible to make a data slicer that it will show revenue of January 2017 and February 2017 when i select February 2017? Belows are the dataset screen shotmy matrix tablemy data

[GilbertQ · Accepted solution]

Hi @CTan42

You could create a measure which will always be the last 2 months from the date you selected. So if you select Feb 2017 it will be for Jan & Feb 2017. Likewise if you had to select Nov 2016, it would then be Oct & Nov 2016.

To do that you could have your base measure and then it would be something similar to this:

Measure =
CALCULATE (
    [Measure Name],
    FILTER (
        ALL ( 'Date'[Calendar Date] ),
               DATEADD('Date'[Calendar Date],+2,MONTH) >= MIN ( 'TableName'[Calendar Date])
        && DATEADD('Date'[Calendar Date],+1,YEAR) <= MAX ( 'TableName'[Calendar Date])
    )
)

You might possibly would have to test the +1 and +2 to get it showing as you want it to.

[CTan42]

@GilbertQ thanks for the help... Do I have to create another Date table? If do so, what should be the content? And what is [measure name]? thanks.

[GilbertQ]

Hi @CTan42

You can follow my blog post below to create a Date Table

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...

And then you can create a relationship between the tables on the Date column to then use the measure.