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

Highlighted
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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)