DAX Fridays! #40: DATESINPERIOD (Moving x Months average)

Thank you, very helpfull video. I am trying to calculate rolling average of already calculated meassure, but average function does not work neither averagex. Coul you please suggest how to solve it?

How about I want to get last 3 months not including current months

i'll be honestl, this video makes absolutely no sense. I'll ignore that fact that you used the "last date" for the "start date". lets just say thats how it works, but you were supposed to be showing a moving average of last 3 months, but you proceeded to show us a moving average of just days. this whole example is in one month (July). how is this a moving average for last 3 months?

Good video. how can we calculate the rolling average of 10 days? when we have data of discontinuous
date. It would be great if you make a video on it.

Curbal - I love your videos for someone like me, who is starting out on using DAX, these videos are lifesavers.
I will have to trouble you with a question though : what is the difference datesbetween and datesinperiod?

Great helpful easy to follow and understand - thank you. I am trying to calculate Bradford factor - please can you advise how to count consecutive days of sickness as single episode. For example if person is off sick from from 5th July 21 to 8th July 21 then this should be = 1 episode and if same person is off sick is 6th Dec 21 to 9 Dec 21 this should equal to 1 episode, so total no of episodes = 2. thanks. I would greatly appreciate help

You are amazing Ruth!

Thanks Ruth! This was a little confusing (visually), but I understood it after a couple of reviews.

Why we use the last date function when we want from the first date to that is from 1996 in this scenario!

Do we need a date table / calendar as a separate table to implement DatesinPeriod?

Hi Ruth. Thanks. This works well if I have a date filter chosen for a particular month and year.. But i have a date slicer which is a range. For this the measure is always zero. Can you help please.

Hello ! Why do you use last date dax to indicate the start date un the fórmula? Very good video

Why you have taken the 'lastdate' as an argument? Can you explain?

Hi regerarding your video i have a task to calcualte moving average. It works fine when i put it on the chart. But when i filter the data on the chart it does not tak innto accout in calcupations earlier dates so the average is not correct. Canyou help?

powerbi file is not available in the download centre. Can you please check.

Thanks Curbal for this video. In same method can you suggest how to calculate moving average count (example count of Customer ID).

Can we make it parameterized??

I see you put in MONTH interval, but in the report, it seems to be taking the daily average?

Very helpful. Thank you for being so comprehensive. I appreciate the granularity.

Good

Good video. I understand the logic but I’m having issues replicating this using COUNTROWS. I want the 1 week and 4 week rolling averages but have yet to get the correct result. I don’t have a particular column to sum, but I need to know the amount of records in a given period.

Hi Ruth, thank you for your amazing videos, but in this video I can't understand one thing. You went for creating a formula for monthly average and you are going day by day moving average. If I see your video correctly it focus on 8th Jul, 12th July instead of May, June and July month sales data moving average. May I know the reason if you kindly explain?

excellent

my total sales are big and in dollar while yours is small number. do we have to calculate average tot sales separately to do the moving average ? i am using the same data set

Hello! What is faster: Calculate or Aversgex in this case?

I want to calculate the total sales last week. How can i do that?.

My bad I can't understand one thing. You went for creating a formula for monthly average and you are going day by day moving average. If I see your video correctly it focus on 8th Jul, 12th July instead of May, June and July month sales data moving average. May I know the reason if you kindly explain?

Hi,

We need a table, which will have all the dates from Jan 2018 ( and -1 year )
Which would be Jan 2017 - jan 2018

With all the dates and the sales amount next to each date.
example:

1-1-17 | 10,000
1-2-17 | 20,000
2-2-17 | 20,000
2-10-17 | 20,000
2-12-17 | 20,000
1-1-18 | 30,000

We used this
calInPeriod
=Calculate(SUM(FactSales[SalesAmount]],DatesInPeriod(date[fullDate],date(2007,01,01),-1,year)

But, does not work
Thanks

Would this work with a SUM function instead of AVERAGE? I am not at work right now so I can't check...

Many thanks!

Hi Ruth! Another great video... Thanks! My understanding here is that, in the Power BI example you are presenting, you are obtaining the daily moving average for the last 12 months, correct? What about if you want to get the monthly moving average for the last 12 moths based? The daily sales would need to be summed to obtain the monthly total and then get the monthly moving average, right?

Hi Ruth, would this formula work if i replace Average (Sales) to Distinct count of the dimensions (lets say customer)? I need to see Running 3 months Distinct count by customer. Appreciate if you can help!

Thanks so much for such informative videos.Your videos are my reference point:))

Hi Ruth, It's a really good video! The column I am trying to get average is not the total value of the item. It's a FIFO layer value (each item has multiple layers of value). The average formula only works on a column of a table (not measure). Is there any way I can nest sum column? example : = calculate(average(sum(FIFO_table,[total item value]) DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]),-30, day))

Why don't you do one complete real time business intelligence application....... which would cover most of the functions... It will be a great learning for us.

Hi.... Nice one ... I didn't knew that bell thing :D

Thanks. Keep the videos coming!

Thank You Ruth !!!

Really cool.

Links do not appear to work as you rightly state probably a browser issue.

Hi Ruth,nice work again,tried your survey link in IE and Chrome but both returned errors... sorry.Great work, really helps me in my work! ps and great fun!!!Will

Good session again Ruth , is The demonstration Colin Taylor showed on your live session going to produce a another video as it looked very interesting but as you know it was hard to follow.
Have a great weekend :-)

Thank You Very Much! Can you please make a video to calculate distance between two Zip-codes.

Hey Ruth ! Another cool video, thanks so much ... it's always fun for,me to start the Saturday then with redoing what you explained on Dax Friday ;-) 🙋🏼‍♂️👍🏻🌞 have a Great Weekend 😎🍹🎉💃🏻

Great video !!! Thumbs Up !!!

Thanks for the DatesInPeriod fun : )

as always awesome video. just so you know the survey link does not work for me either... using Chrome i get the following error : {"error":{"code":"generalException","message":"General Exception While Processing"}}

Hi Ruth : Great video..... thanks for the moving average refresher!

very helpful ma'am... thanks :)