Need help in a visual project :)

[Ishallown]

hi everyone im having issue to show the visual below as im pretty new to Power BI

i would like to show by country (legend) the simple moving averages (value ) and the shared axis being the report run date.

below is the sample data

Payer Number	CountryCode	Report Run Date	TotalExposureUSD
1	SK01	31-08-20	0.022227
1	SK01	28-08-20	0.022227
1	SK01	26-08-20	0.022227
1	SK01	25-08-20	0.022227
1	SK01	21-08-20	0.022227
1	SK01	19-08-20	0.022227
1	SK01	17-08-20	0.022227
1	SK01	14-08-20	0.022227
1	SK01	12-08-20	0.022227
1	SK01	10-08-20	0.022227
1	SK01	07-08-20	0.022227
1	SK01	05-08-20	0.022227
1	SK01	03-08-20	0.022227
1	SK01	31-07-20	0.01667
1	SK01	29-07-20	0.01667
1	SK01	27-07-20	0.01667
1	SK01	22-07-20	0.01667
1	SK01	20-07-20	0.01667
1	SK01	17-07-20	0.01667
1	SK01	15-07-20	0.01667
1	SK01	13-07-20	0.01667
1	SK01	10-07-20	0.01667
1	SK01	08-07-20	0.01667
1	SK01	06-07-20	0.01667
1	SK01	03-07-20	0.01667
1	SK01	01-07-20	0.01667
1	SK01	29-06-20	0.011113
1	SK01	24-06-20	0.011113
1	SK01	22-06-20	0.011113
1	SK01	19-06-20	0.011113
1	SK01	17-06-20	0.011113
1	SK01	15-06-20	0.011113
1	SK01	12-06-20	0.011113
1	SK01	10-06-20	0.011113
1	SK01	08-06-20	0.011113
1	SK01	01-06-20	0.011113
1	SK01	29-05-20	0.005557
1	SK01	22-05-20	0.005557
1	SK01	15-05-20	0.005557
1	SK01	08-05-20	0.005557
1	SK01	30-04-20	0
1	SK01	24-04-20	0
1	SK01	16-04-20	0
1	SK01	07-04-20	0
2	SK01	03-07-20	0.270229
2	SK01	06-07-20	0.270229
2	SK01	08-07-20	0.270229
2	SK01	10-07-20	0.32537
2	SK01	13-07-20	0.32537
2	SK01	22-07-20	0.370226
2	SK01	15-07-20	0.32537
2	SK01	27-07-20	0.370226
2	SK01	17-07-20	0.370226
2	SK01	29-07-20	0.370226
2	SK01	07-04-20	0
2	SK01	16-04-20	0
2	SK01	24-04-20	0
2	SK01	30-04-20	0
2	SK01	08-05-20	0.005557
2	SK01	15-05-20	0.005557
2	SK01	22-05-20	0.057414
2	SK01	29-05-20	0.057414
2	SK01	01-06-20	0.06297
2	SK01	08-06-20	0.11458
2	SK01	10-06-20	0.11458
2	SK01	12-06-20	0.11458
2	SK01	15-06-20	0.163029
2	SK01	19-08-20	0.412584
2	SK01	17-06-20	0.163029
2	SK01	19-06-20	0.213209
2	SK01	22-06-20	0.213209
2	SK01	24-06-20	0.213209
2	SK01	29-06-20	0.264672
2	SK01	01-07-20	0.270229
2	SK01	20-07-20	0.370226
2	SK01	31-07-20	0.370226
2	SK01	03-08-20	0.412584
2	SK01	05-08-20	0.412584
2	SK01	07-08-20	0.412584
2	SK01	10-08-20	0.412584
2	SK01	12-08-20	0.412584
2	SK01	14-08-20	0.412584
2	SK01	17-08-20	0.412584
2	SK01	21-08-20	0.412584
2	SK01	25-08-20	0.412584
2	SK01	26-08-20	0.412584
2	SK01	28-08-20	0.412584
2	SK01	31-08-20	0.412584
4	IN02	01-06-20	0.1234
4	IN02	02-06-20	0.564
4	IN02	03-06-20	0.225
4	IN02	07-07-20	0.1553
4	IN02	08-07-20	0.169
4	IN02	09-07-20	0.122
4	IN02	01-08-20	0.1365
4	IN02	02-08-20	0.188
4	IN02	03-08-20	0.889
4	IN02	04-08-20	0.954
4	IN02	05-08-20	0.44

many thanks for the help provided 🙂

[v-yingjl · Accepted solution]

Hi @Ishallown ,

You can create this measure:

Measure = 
CALCULATE(
    AVERAGE('Table'[Total Exposure USD]),
    ALLEXCEPT(
        'Table',
        'Table'[Report Run Date]
    )
)

Use a line chart to show the result:

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ishallown , not sure what is the challenge , refer this

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart

You can have Avg like

Cumm Avg= CALCULATE(Average(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Avg= CALCULATE(Average(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

Cumm Sales = CALCULATE(Sum(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date]))) / CALCULATE(distinctcount(Sales[Sales Date]]),filter(date,date[date] <=max(Sales[Sales Date])))

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day)) /7

Rolling 7 day = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day))

[Fowmy]

@Ishallown 

Add a Dates Table to your model and link to your date and create the below formula, I made the past  7 days average, you can change as necessary.

Moving AVerage = 

AVERAGEX(
    DATESINPERIOD('Calendar'[Date] , LASTDATE('Calendar'[Date] ) ,-7,DAY),
    CALCULATE(SUM('Table'[TotalExposureUSD])
)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Ishallown]

Hi Fowmy,

would you be able to explain in terms of what is going on within the formula as i want to learn what is going on . sorry for the trouble im a newbie with dax

thanks ! 

[Fowmy]

@Ishallown 

The measure calculates the average based on the date window of the past 7 days.
The DATESINPERIOD function creates that date window from the current point on the axis -t days if you are on the chart say 10th July, it takes from 10th July back to 4th July.
Then, the AVERAGEX add each USD Value and gives the average of it
------------------------------------------------------
Moving AVerage = 

AVERAGEX(
    DATESINPERIOD('Calendar'[Date] , LASTDATE('Calendar'[Date] ) ,-7,DAY),
    CALCULATE(
        SUM('Table'[TotalExposureUSD])
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AiolosZhao]

Hi @Ishallown ,

Could you please tell me the logic of your simple moving averages?

It's better to clarify it using your sample data and please also help to show the desired results.

Thanks.

Aiolos Zhao