cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chakrabmonoj
Helper II
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

 

View solution in original post

12 REPLIES 12
cjulianm
Advocate I
Advocate I

Do you have separate Calendar table?

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

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")

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.

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

 

Thanks

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


Thank you in advance

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

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.

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

Average Lead Time =
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! 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.