Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help in a visual project :)

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 NumberCountryCodeReport Run DateTotalExposureUSD
1SK0131-08-200.022227
1SK0128-08-200.022227
1SK0126-08-200.022227
1SK0125-08-200.022227
1SK0121-08-200.022227
1SK0119-08-200.022227
1SK0117-08-200.022227
1SK0114-08-200.022227
1SK0112-08-200.022227
1SK0110-08-200.022227
1SK0107-08-200.022227
1SK0105-08-200.022227
1SK0103-08-200.022227
1SK0131-07-200.01667
1SK0129-07-200.01667
1SK0127-07-200.01667
1SK0122-07-200.01667
1SK0120-07-200.01667
1SK0117-07-200.01667
1SK0115-07-200.01667
1SK0113-07-200.01667
1SK0110-07-200.01667
1SK0108-07-200.01667
1SK0106-07-200.01667
1SK0103-07-200.01667
1SK0101-07-200.01667
1SK0129-06-200.011113
1SK0124-06-200.011113
1SK0122-06-200.011113
1SK0119-06-200.011113
1SK0117-06-200.011113
1SK0115-06-200.011113
1SK0112-06-200.011113
1SK0110-06-200.011113
1SK0108-06-200.011113
1SK0101-06-200.011113
1SK0129-05-200.005557
1SK0122-05-200.005557
1SK0115-05-200.005557
1SK0108-05-200.005557
1SK0130-04-200
1SK0124-04-200
1SK0116-04-200
1SK0107-04-200
2SK0103-07-200.270229
2SK0106-07-200.270229
2SK0108-07-200.270229
2SK0110-07-200.32537
2SK0113-07-200.32537
2SK0122-07-200.370226
2SK0115-07-200.32537
2SK0127-07-200.370226
2SK0117-07-200.370226
2SK0129-07-200.370226
2SK0107-04-200
2SK0116-04-200
2SK0124-04-200
2SK0130-04-200
2SK0108-05-200.005557
2SK0115-05-200.005557
2SK0122-05-200.057414
2SK0129-05-200.057414
2SK0101-06-200.06297
2SK0108-06-200.11458
2SK0110-06-200.11458
2SK0112-06-200.11458
2SK0115-06-200.163029
2SK0119-08-200.412584
2SK0117-06-200.163029
2SK0119-06-200.213209
2SK0122-06-200.213209
2SK0124-06-200.213209
2SK0129-06-200.264672
2SK0101-07-200.270229
2SK0120-07-200.370226
2SK0131-07-200.370226
2SK0103-08-200.412584
2SK0105-08-200.412584
2SK0107-08-200.412584
2SK0110-08-200.412584
2SK0112-08-200.412584
2SK0114-08-200.412584
2SK0117-08-200.412584
2SK0121-08-200.412584
2SK0125-08-200.412584
2SK0126-08-200.412584
2SK0128-08-200.412584
2SK0131-08-200.412584
4IN0201-06-200.1234
4IN0202-06-200.564
4IN0203-06-200.225
4IN0207-07-200.1553
4IN0208-07-200.169
4IN0209-07-200.122
4IN0201-08-200.1365
4IN0202-08-200.188
4IN0203-08-200.889
4IN0204-08-200.954
4IN0205-08-200.44


many thanks for the help provided 🙂

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    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.

6 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        Anonymous 

        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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , 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))

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    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.