cancel
Showing results for
Did you mean:
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

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

Hi @Karthiknfr ,

Enter a table like this:

 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.
3 REPLIES 3
Highlighted
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

Thanks

Community Support Team

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

Hi @Karthiknfr ,

Enter a table like this:

 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.
Regular Visitor

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

@v-frfei-msft  It did not work.

Announcements

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

#### 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

Find out where you can attend!

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