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

[chakrabmonoj]

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 

[cjulianm]

Do you have separate Calendar table?

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

[chakrabmonoj]

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?

[cjulianm]

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

[chakrabmonoj]

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

[cjulianm]

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.

[chakrabmonoj]

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

[cjulianm · 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

[Aminomidvar]

This formula will not calculate the average correctly on the first two months of your data (here for Jan and Feb 2024).
To fix it you need to create a measure called count.

count =

SWITCH(

TRUE(),

SELECTEDVALUE(Table1[Date]) = DATE(2024, 1, 1), 1,

SELECTEDVALUE(Table1[[Date]) = DATE(2024, 2, 1), 2,

3

)

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

[JoseAndres]

Thanks

[mluanacruz]

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

[cjulianm]

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

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

[mluanacruz]

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.

[justinleow]

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

[Clinical_Epi]

Hi - I know this post is ancient - however did you find a solution to your issue?

I'm finding the LASTDATE example does work in latest power bi (March 2023)

when using a percentage measure...

very frustrating....

so for me this:

         CALCULATE(FORMAT([HAC MoM Rate (MTD)]/[Target MOM 0.035],"▼00.00%;▲00.00%"),

            DATESINPERIOD(

            'Date'[Date],

            LASTDATE('Date'[Date]),-3,MONTH)

and this

CALCULATE(FORMAT([HAC MoM Rate (MTD)]/[Target MOM 0.035],"▼00.00%;▲00.00%"),

            DATESINPERIOD(

            'Date'[Date],

            LASTDATE('Date'[Date]),-1,MONTH)

Give me exactly the same results... (no page filters being used)

Any other ideas (that dont involve forcing the last date to work)... ?

kind regards

Clin Epi

[Clinical_Epi]

Ah you know using MTD (totalmtd) in measures only works outside of this logic - ha, answered my own question in the end..

The LASTDATE formula works fine as long as you dont use totalmtd in the measure its refering to lol...