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
CTan42
Helper II
Helper II

Date Slicer to show 2 months revenue

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 matrix tablemy datamy data

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

3 REPLIES 3
GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@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.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.