R3m vs R12m toggle as a slicer in Line Chart

[Anonymous]

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

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

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

[Anonymous]

@v-frfei-msft  It did not work. 

[Anonymous]

---

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

---