cancel
Showing results for
Did you mean:  Helper II

## dynamically calculate average for last 3 months sales and last 6 months sales

I want to calculate the average for L3 and L6, dynamically, which means with every new month sales value added in the table, BI automatically adjusts the period to calculate the average for last 3 months and 6 months. E.g. In Jan, it should calculate L3 as average{nov,dec,jan} and L6 as {aug,sep,oct,nov,dec,jan}; similarly, when I update the feb values in this table, upon refreshing, the DAX formula should automatically adjust the period as follows : L3{dec,jan.feb} and L6{sep,oct,nov,dec,jan,feb}

The additional problem is that the year field in my table is in numeric type and the month field is in text type and I am not able to convert or combine either of the fields to date type, so that I may use any of the date-based DAX functions

1 ACCEPTED SOLUTION  Assuming you only have 1 table - Table1 with column headers [Sales], [Date] then adding new measures:

3mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-3,MONTH))/3

6mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-6,MONTH))/6

12mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))/12

24mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-24,MONTH))/24

12 REPLIES 12  Do you have separate Calendar table?

3mth = CALCULATE(SUM(sales[sale]),DATESINPERIOD(Calendar[Date],LASTDATE(Calendar[Date]),-3,MONTH))/3  Helper II

Julian - nope , I dont't have a calendar table...what is that and do I have it? will this help the solution?

How do you set up the calendar table and connect it to my data table?  No you dont need one. There is alot of information/discussion on this topic - see this link

You could alway add a new column  Date = FORMAT(Table1[Column1] &"-"& Table1[Column2],"MM-YYYY")  Helper II

Julian - thanks for this.

couple of questions :

1. When u say "add column" - this is thru' the "add column" option under the "modelling" tab, right?

2. in your formula, what would be my corresponding columns for [column1] and [column2]? - should I have column1 filled with the names of the month, and column2, filled with the years?

3. i have 2 columns - year , filled with years but in numeric format and month, filled with names of the month, but in txt format - hope your solution will work on these columns

thanks  Yes, thats right "add column" from modelling tab. Put your month header name into [column1] and year into [column2], and the table name in front of each. "Format MM-YYY" sorts out the text and numeric formats.  Helper II

so now I have a column added called [date]. So in your original formula, the calendar is a function or it should be replaced with the name of the table , [date], is now added to?

thanks  Assuming you only have 1 table - Table1 with column headers [Sales], [Date] then adding new measures:

3mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-3,MONTH))/3

6mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-6,MONTH))/6

12mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-12,MONTH))/12

24mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-24,MONTH))/24 New Member

Thanks  Helper I

Hi @cjulianm
This is pretty much everything I am also trying to accomplish. But instead of "Table [Sale]" I need to get the average 3, 6, 12, and 24 from a measure "Ratio_Measure" I created.
This formula 3mth = CALCULATE(SUM(Table1[sale]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-3,MONTH))/3 is not working for me properly. Do you think you can help me?

I have a sample of my excel data and Power BI here at this link HERE

Michelle  Best practice is to create a Calendar table. then use the

3mth = CALCULATE([Ratio_Measure],DATESINPERIOD(Calendar[Date],LASTDATE(Calendar[Date]),-3,MONTH))/3  Helper I

Hi @cjulianm,
Thank you for the help on this.

I found this measure below for the Average of 6 I needed, but do you know if there is a way we can make this measure a little bit more dynamic? Like, do some sort of variable where I can change the number of months I need? this project I am working on I need to take lots of different averages.

Ratio_Measure 6mth = CALCULATE( [Ratio_Measure] ,DATESINPERIOD(PD_agg_perf_measures[Accident_Date],eomonth(MAX(PD_agg_perf_measures[Accident_Date]),-3) ,-3,MONTH))

Where we can change just the variable average number:

ex: AVG_Num = 12
Ratio_Measure 6mth = CALCULATE( [Ratio_Measure] ,DATESINPERIOD(PD_agg_perf_measures[Accident_Date],eomonth(MAX(PD_agg_perf_measures[Accident_Date]),AVG_Num ) ,AVG_Num ,MONTH)) -- this doesn't work but I am trying to get the average 6,12,24... without modifying too much the measure. Is this possible?

Hope I am not overcomplicating things. Frequent Visitor

@cjulianm Hi cJulianm, my apologies for brining this up again. I am very new to DAX and powerBI trying my best to find my way. Similiarly I am trying to calculate the average of the last 3 month's data. And have tried to follow you solutions.

CALCULATE(average('View Shipments'[Total Transit Time]), DATESINPERIOD('Date Table'[Date], max('View Shipments'[Shipped (ASN)]),-3,MONTH))

However,I did not use the "Latestdate" command becuase my date column "Shipped (ASN)" contains duplicates, therefore "Datesinperiod" also give me error that there is duplicate.  I created a calander table from 2010 - 2020, and used MAX instead and no error come out from this.

However, I tried to double check with my Excel to see of the calculation is correct. But it is not correct, would hope that you can help me why my formula isnt working. Thank you so much! Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (5,876)