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


 

Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)