cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 others find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
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 others 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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,717)