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

[@Unbox747]

You are amazing Ruth!

[@CurbalEN]

You too!!

[@jaimeortega6450]

Cuando tengo duda con una Funcion de DAX el primer lugar que busco es Curbal en KZbin. Encontre justo lo que estaba buscando para calcular Turn Over Rate. You are the BEST!

[@chakrabmonoj]

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?

[@aks541]

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?

[@mehdiali3891]

Same doubt

[@excelisfun]

Thanks for the DatesInPeriod fun : )

[@CurbalEN]

+ExcelIsFun Nice that you haven't done that one 😂 /Ruth

[@mannarmannan65]

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.

[@Tanya-og7no]

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

[@martynepoffpoff]

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?

[@juandavidangell.5312]

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

[@growwmore1205]

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

[@nikhargesumit9069]

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

[@sandyarani936]

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?

[@Graylinepartners]

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

[@CurbalEN]

Thanks! /Ruth

[@solimar81]

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

[@YoUzHi90]

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!

[@myleshuet7999]

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.

[@kirankumari1985]

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

[@anjutamang2542]

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

[@jazzista1967]

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

[@CurbalEN]

+jazzista1967 Hi! Thanks! Glad you liked it and happy Friday :) /Ruth

[@martinmejia197]

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?

[@mathijs9365]

Thanks. Keep the videos coming!

[@CurbalEN]

+Mathijs Thanks to you for watching :) /Ruth

[@kirtivaghela877]

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

[@peterg4130]

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

[@CurbalEN]

Sure, it should :) /Ruth

[@orxanbabashov]

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

[@CurbalEN]

+Orxan Babashov Fantastic! You have no idea how glad that makes me ! Have a great day :) /Ruth

[@olcia2467]

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?

[@AnoopKumar-rf1zn]

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.

[@SmartBimson]

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 😎🍹🎉💃🏻

[@CurbalEN]

+SmartBimson You start your saturdays with a Dax Friday video? I might have to move them to Saturdays then 😂 /Ruth

[@SmartBimson]

Curbal dios mio nooo 😂😂 the routine is: going home and watching dax Friday ... then I think about how and where I can use it for my own projects. On Saturday - since I'm an early bird - I start with a tea and i practice what I saw on Friday . Usually it gives me really good ideas or even make me more curious what else is possible. And every Friday I'm surprised what else is possible actually 👍🏻🙋🏼‍♂️ so please don't change to dax sabado because my relaxing mood will start toooooo late then 😂😂😉

[@CurbalEN]

+SmartBimson Ok no DAX sábados then :) /Ruth

[@aonline-training]

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

[@CurbalEN]

There are a lot of ways on how you could do that depending on your model. You could use DATEADD: curbal.com/blog/glossary/dateadd-dax DATESBETWEEN: curbal.com/blog/glossary/between-dax ... Post in the power bi community for more specific feedback! /Ruth

[@automationguide3498]

Thank You Ruth !!!

[@CurbalEN]

+Harsha's VBA Guide You welcome! :) /Ruth

[@Victor-ol1lo]

Great video !!! Thumbs Up !!!

[@CurbalEN]

+Victor Friesen You welcome! Have a great weekend! /Ruth

[@mandarvv]

Can we make it parameterized??

[@TheDistractionStudio]

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

[@CurbalEN]

+rajan77 Hi Rajan, absolutely, added to my list now :) /Ruth

[@leonardofolina6786]

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

[@MrNillock]

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

[@CurbalEN]

+Collin Taylor Mmmmm why is it working for me?? Ok, I will check it out. Thanks for letting me know and happy Friday :) /Ruth

[@MrNillock]

looks like its linking to your personal OneDrive... did you produce the link with the share option or just copy the link? when clicking share you can mange the access rights for anyone who clicks it. and Happy Friday!!! its my day off today if you want to try again :)

[@CurbalEN]

+Collin Taylor Share option, but I can produce a new link and see if that works. I am having dinner with some friends tonight so let's try again next week :) /Ruth

[@MrNillock]

enjoy your dinner :) and happy friday!

[@CurbalEN]

+Collin Taylor The same and say hi 👋 to your lovely daughter! /Ruth

[@bradj229]

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

[@CurbalEN]

Glad it worked at the end :)

[@denysss5872]

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

[@CurbalEN]

It depends on your data and model. Create both and measure them in Dax studio to be sure. /Ruth

[@feng3625]

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

[@fionamehta7381]

Many thanks!

[@CurbalEN]

You welcome :) /Ruth

[@deepakverma7636]

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

[@markdawson8876]

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

[@CurbalEN]

+Mark Dawson Hi Mark, Yes, we are going to do a recording next week, so I will publish perhaps on Wednesday.

[@CloudhoundCoUk]

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

[@CurbalEN]

+Cloud Hound Thanks Christopher, the odd thing is that they work for me...I will check them on Monday :) /Ruth

[@williampetit3234]

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

[@CurbalEN]

+William Petit so strange! It works perfectly for me...I will create a new Link and try again on the next video. The best way to learn is to have fun so if you are getting both you will become a DAX master in no time :) Have a great weekend! /Ruth

[@MarkCBB86]

Very helpfull, thank you

[@CurbalEN]

+Mark Blackburn Excellent! Glad you liked it :) Happy Friday :) /Ruth

[@nancysapra5555]

very helpful ma'am... thanks :)

[@CurbalEN]

+nancy arora Hi Nancy! Excellent! Happy Friday :) /Ruth

[@aimantabreez9439]

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

[@rrrprogram8667]

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

[@CurbalEN]

+RRR program Yes, quite annoying actually! They changed that without telling people, so we don't get notifications anymore ... /Ruth

[@rrrprogram8667]

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.

[@CurbalEN]

+RRR program All my beginners vídeos are like that, have you seen them? I will definitely do more, they just take a lot of time to record and edit and therefore they are not so easy to produce, but I will definitely do more in the autumn :) /Ruth

[@stevennye5075]

excellent

[@CurbalEN]

Thanks! /Ruth

[@mubeenxp8502]

Good

[@lewinmg]

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?