How to calculate last 3 days fixed (Not running avg) average price based in date slicer

[gnandhu]

Hi,

Here is the sample data, referring daily price by application usage.

Application     UsageDate         Price
---------------------------------------------------
MyApp1         2017-03-07       10
MyApp1         2017-03-06       15
MyApp1         2017-03-05       17

MyApp1         2017-03-04       20
MyApp1         2017-03-03       21
MyApp1         2017-03-02       12
MyApp1         2017-03-01       14

MyApp2         2017-03-07       10
MyApp2         2017-03-06       15
MyApp2         2017-03-05       17
MyApp2         2017-03-04       20
MyApp2         2017-03-03       21
MyApp2         2017-03-02       12
MyApp2         2017-03-01       14

I want to plot this data in Line chart with 2 lines. 

Line 1 represents daily price

Line 2 represents avg price 

In the report there are two slicers , one is Application and the other one is Usagedate

When we select the application "MyApp1" and date "2017-03-06", then the Line Chart should contains two lines, 

Line1 should plot last 3 days daily price, means line1 need to show the data from "2017-03-03" to '2017-03-06"

Line2 should plot average price (fixed) for last 3 days. (15+17+20+21)/4 = 73/4 = 18.25

Can any one advise?

Thanks,

[v-huizhn-msft]

Hi @gnandhu,

I try to reproduce and get expected result, In following steps, 

First, please click "New Table" under Modeling on home page, type the following formula and create a new table.

Datetable = DISTINCT(SELECTCOLUMNS(Table2,"Date",Table2[UsageDate]))

2. Create calculated column to get 3 days ago date.

RANK = RANKX(Datetable,Datetable[Date],,ASC)

Last 3 days = LOOKUPVALUE(Datetable[Date],Datetable[RANK],Datetable[RANK]-3)

3. Create a slicer including Datetable[Date], and another slicer including Table2[Application]

Create a measure select the value clicked by you, another measure to get 7 days ago date selected.

get = CALCULATE(MAX(Datetable[Date]),ALLSELECTED(Datetable[Date]))

Last 3 days selected = CALCULATE(MAX(Datetable[Last 3 days]),ALLSELECTED(Datetable))

4.  Create three measures.

select price = CALCULATE(SUM(Table2[Price]),FILTER(Table2,AND(Table2[UsageDate]<=Datetable[get],Table2[UsageDate]>=Datetable[Last 3 days selected])))

Measure 3 = CALCULATE(AVERAGE(Table2[Price]),FILTER(ALLEXCEPT('Table2',Table2[Application]),AND(Table2[UsageDate]<=Datetable[get],Table2[UsageDate]>=Datetable[Last 3 days selected])))

Average1 = IF(CALCULATE(MAX(Table2[UsageDate]),ALLEXCEPT(Table2,Table2[UsageDate]))<=[get] && CALCULATE(MAX(Table2[UsageDate]),ALLEXCEPT(Table2,Table2[UsageDate]))>=[Last 3 days selected],[Measure 3],BLANK())

 
Create a line chart, select Table2[UsageDate] as axis level, [select price] and the [Average1] as value level. You will get expected result when you select different values in slicer.

Best Regards,
Angelia

[gnandhu]

Thank you Angelia.

For this example, solution is working but when I try to replica this to my scenario, avg is not working properly. I will try again and will update the same.

Regards,

gnandhu

[v-huizhn-msft]

Hi @gnandhu,

Do you resolve your issue? If it doesn't, please your sample data or .pbix file for further analysis.

Best Regards,
Angelia