Forum Discussion

beast314's avatar
beast314
Frequent Visitor
5 years ago
Solved

calculating run rate

Hi all,

I am new to Power Bi and this may seem like nothing but, I am stuck on a problem and I have no idea how to proceed further with it. So I have live sales data for the 90 days from which I have to calculate the run rate of the last 15 and 30 days (i.e. TODAY - 15, TODAY-30).

Also, if on days no sales happen then I have to not count that day. so if in the last 7 days only 5 days sales had happened, then I will have to take (Sum of sales Quantity)/5.

 

General Comment
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi beast314 ,

     

    My previous measure works in this sample. Do I miss anything?

    Note: I didn't return any value for year level in the Matrix value, because I don't know your calculation logic for year.

     

     

    Best Regards,

    Icey

13 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    beast314 

    Can you share some sample data with the desired output to have a clear understanding of your question? Mention whether you want a calculated column or measure
    You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.


  • beast314's avatar
    beast314
    Frequent Visitor
    5 years ago

    Fowmy 

    It is something like it

    Date                 Sales_Quantity

    12/07/2021           10

    11/07/2021             5

    10/07/2021             <Blank>

    09/07/2021             8

    08/07/221               6

    07/07/2021              8

    06/06/2021              2

     

    If such data is given to me then I want a calculated column of (10+5+8+6+8+2)/6  =39/6 = 6.5

    I have data in segment-wise so for each column I need the run rate. I don't care as long as I can present it in the matrix form. Like for segment A-> (11+8+3)/3 = 7.33

                                                     B-> (14+13+14)/3 = 13.66 

                                                      C->(3+7)/2 = 5

    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      beast314 

      You either use simple average as it excludes blanks 

      Run Rate =  AVERAGE( Table6[Quantity] )

       
      Or specify explicitly 

      Run Rate = 
CALCULATE(
    AVERAGE( Table6[Quantity] ),
    Table6[Quantity] > 0
)
      • beast314's avatar
        beast314
        Frequent Visitor
        5 years ago

        Fowmy 

        How to put the date condition in this.

        This table was made by making a measure

        Quant30 = CALCULATE( SUM('Table'[Quantity]), FILTER( ALL('Table'[date]),'Table'[date]> TODAY()-30))
        How can I take an average of measure?
  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi beast314 ,

     

    How about this?

    Measure 2 =
IF (
    ISFILTERED ( 'Table'[Date].[Day] ),
    [Quant30],
    IF ( ISFILTERED ( 'Table'[Date].[Month] ), AVERAGEX ( 'Table', [Quant30] ) )
)

     

     

    Best Regards,

    Icey

     

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

      • Icey's avatar
        Icey
        Community Support
        5 years ago

        Hi beast314 ,

         

        What exactly does not meet your requirements? Could you show me more details?

         

         

        Best Regards,

        Icey