cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Karthiknfr Regular Visitor
Regular Visitor

R3m vs R12m toggle as a slicer in Line Chart

Hello,

 

I am trying to create a line chart that varies with slicer selected. Let me explain briefly

 

I have created measures as follows: 

Period Start Dates_Dim = FIRSTDATE( DATESINPERIOD(Dates_Dim[Date], [Period End Dates_dim], -3, MONTH))
Period End Dates_dim = LASTDATE(Dates_Dim[Date])
R12 Period Start Dates_Dim = FIRSTDATE( DATESINPERIOD(Dates_Dim[Date], [Period End Dates_dim], -12, MONTH))
 
Rolling 12 Month Average Dates_Dim = divide(CALCULATE(SUM(Oracle_Order_Management[Sales]),DATESBETWEEN ( Dates_Dim[Date], [R12 Period Start Dates_Dim], [Period End Dates_dim] )),12,0)
 
Rolling 3 Month Average Dates_Dim = divide(CALCULATE(SUM(Oracle_Order_Management[4WE Quantity]),DATESBETWEEN ( Dates_Dim[Date], [Period Start Dates_Dim], [Period End Dates_dim] )),3,0)
 
Now in Line chart i have added Month_Name as Axis, Products in Legend and Rolling 3 Month Average Dates_Dim as values.
------------------------------------------------------------------------------------------------------------------------------------------
 
But what i need is i need to display rolling 3 months avg or rolling 12 months average based on Slicer. So i need help with
 
  1. How do i create a button or slicer which has R3m and R12m as selection?
  2. My Line chart should change according to slicer
  3. What values should i add to Values field in slicer?

Hope someone can help me with his soon.

 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: R3m vs R12m toggle as a slicer in Line Chart

Hi @Karthiknfr ,

 

Enter a table like this:

1.PNG

 

Slicer
rolling 3 months avg
rolling 12 months average

 

After that, Create a new measure as below to get the excepted result.

measure =
VAR se =
    SELECTEDVALUE ( 'Slicer'[Slicer] )
RETURN
    IF (
        NOT ( ISFILTERED ( 'Slicer'[Slicer] ) ),
        BLANK (),
        IF (
            se = "rolling 3 months avg",
            [Rolling 3 Month Average Dates_Dim],
            IF ( se = "rolling 12 months average", [Rolling 12 Month Average Dates_Dim] )
        )
    )
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Karthiknfr Regular Visitor
Regular Visitor

Re: R3m vs R12m toggle as a slicer in Line Chart

 


@Karthiknfr wrote:

Hello,

 

I am trying to create a line chart that varies with slicer selected. Let me explain briefly

 

I have created measures as follows: 

Period Start Dates_Dim = FIRSTDATE( DATESINPERIOD(Dates_Dim[Date], [Period End Dates_dim], -3, MONTH))
Period End Dates_dim = LASTDATE(Dates_Dim[Date])
R12 Period Start Dates_Dim = FIRSTDATE( DATESINPERIOD(Dates_Dim[Date], [Period End Dates_dim], -12, MONTH))
 
Rolling 12 Month Average Dates_Dim = divide(CALCULATE(SUM(Oracle_Order_Management[Sales]),DATESBETWEEN ( Dates_Dim[Date], [R12 Period Start Dates_Dim], [Period End Dates_dim] )),12,0)
 
Rolling 3 Month Average Dates_Dim = divide(CALCULATE(SUM(Oracle_Order_Management[4WE Quantity]),DATESBETWEEN ( Dates_Dim[Date], [Period Start Dates_Dim], [Period End Dates_dim] )),3,0)
 
Now in Line chart i have added Month_Name as Axis, Products in Legend and Rolling 3 Month Average Dates_Dim as values.
------------------------------------------------------------------------------------------------------------------------------------------
 
But what i need is i need to display rolling 3 months avg or rolling 12 months average based on Slicer. So i need help with
 
  1. How do i create a button or slicer which has R3m and R12m as selection?
  2. My Line chart should change according to slicer
  3. What values should i add to Values field in slicer?

Hope someone can help me with his soon.

 

One more issue which just found was that if i selected any particular month it is showing only for that month. Where as i wanted it to change and show values for last 3 months or 12 months. Like if i selected june i wanted it to display April, May and June

 

@Stachu 

@v-piga-msft 

@v-xicai 

@jdbuchanan71 

@SivaMani 

 

Thanks


 

Community Support Team
Community Support Team

Re: R3m vs R12m toggle as a slicer in Line Chart

Hi @Karthiknfr ,

 

Enter a table like this:

1.PNG

 

Slicer
rolling 3 months avg
rolling 12 months average

 

After that, Create a new measure as below to get the excepted result.

measure =
VAR se =
    SELECTEDVALUE ( 'Slicer'[Slicer] )
RETURN
    IF (
        NOT ( ISFILTERED ( 'Slicer'[Slicer] ) ),
        BLANK (),
        IF (
            se = "rolling 3 months avg",
            [Rolling 3 Month Average Dates_Dim],
            IF ( se = "rolling 12 months average", [Rolling 12 Month Average Dates_Dim] )
        )
    )
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Karthiknfr Regular Visitor
Regular Visitor

Re: R3m vs R12m toggle as a slicer in Line Chart

@v-frfei-msft  It did not work. 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 149 members 1,714 guests
Please welcome our newest community members: