calculating run rate

[beast314]

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.

[Icey]

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.

[beast314]

No. It not working. 

[Icey]

Hi @beast314 ,

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

Best Regards,

Icey

[beast314]

It's showing me a complete blank on that.  

[Icey]

Hi @beast314 ,

Could you create a sample .pbix with the same data and model structure for me to test? Maybe there is something missing in my sample above.

For your data security, please don't use real data or contain any sensitive information in the sample .pbix file.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

[beast314]

https://drive.google.com/file/d/1expali_JN2VePfdyijyjFoEL29udjNdd/view?usp=sharing 

Here is a sample dataset. Note that I want a 30-day average region-wise.

[Icey · Accepted solution]

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

[beast314]

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

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

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

[Fowmy]

Quant30 = CALCULATE( Average('Table'[Quantity]), FILTER( ALL('Table'[date]),'Table'[date]> TODAY()-30 && 'Table'[date]< TODAY()),

Table'[Quantity] > 0

)

[beast314]

@Fowmy 

when I am applying this. It is giving me all 1.

[Fowmy]

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