This is the best Virtual table demo I've used and got me over the hump. I've been fighting this problem by burdening my model with helper tables in power query. This approach with DAX will level me up to take advantage of measures and the correct row context. THANK YOU!
Love your style Mitchell!! Thank you so much for these videos & great explanations. Hungry for more :). Greetings from Czech Republic / Europe. Martina
Thank you for another great video Mitchell. I am really fond of your teaching style, the way you lay it out all in a nice and simple way. It sure sticks to the brain. Looking forward to watching more of it.
This is awesome! I've been looking at how to show month average by year for a bit and I can't understand how everything works together. You just explained everything clearly, not just the average but the use of multiple useful combinations that can be applied in complicated calculation. Thanks!!!
Thank you Mitchell for such an awesome video. I have learnt a lot from your teachings at Pragmatic Works. Your explanations are very easy to understand. You can explain difficult topics very easily.
22' very well spent. Among other things, I appreciated the note on SUMMARIZECOLUMNS interaction of the context. Thanks Mitchell, I just subscribed your channel
Great video and thanks for taking the time to explain everything in detail. I know this video is a couple years old. Is there a way to not return a 3 month average for the first 2 rows where there is not enough previous data to give 3 month average?
it would be great to get the files that you are using within your videos. according to me you are the one of the best in terms of teaching the background of the functions in daX
This was a great tutorial! You really took time to go through every line of code & explain. Would you ever consider doing an average by week? Again thank you for posting this :)
Hi, I congratulate you for the really interesting topic. I would like to ask you a question. How can I apply this formula create with DAX with Excel Power Query that uses M language? Tnx for your reply!
I know I am 2 years behind 😂😂😂. But I want to see everything you touch, because everything you touch turns to golden knowledge. 😂😂 maybe co-pilot vs. Dax video
Hi @Mitchell, this is Raj from India. I need to achieve some scenario. Let us assume 5 products were sold in the last month and 2 of them were sold in current month also along with other products. Now, I need to exclude those 2 products and retrieve the count of last month sold products as 3 instead of 5. eg: P1, P2, P3, P4 & P5 were sold in the last month. P1, P2, P6 & P7 were sold in current month. Now I need to get the count of last month sold products but not this month as 3(only P3, P4 & P5 should be counted). Please help me write the DAX measure code on this. Thanks in advance.
Hi, I know you mentioned values at the beginning, but is there any advantage to using Summarize? I would just have used AVERAGEX( VALUES( Calendar [ MMM-YYYY] ) , [Total Sales] ) , I find Summarize a bit hard, but have found it useful in reducing the rows of a table that need to be iterated over, so in a a total sales iterate over the sales table summarized by the dim product , customer etc.
Hi Thanks for your videos. I would like to ask a question if I may. I would be interested in how RLS works with SSAS live connection and import mode. Is there a chance you can address this topic in a video? best regards and stay safe
I would like to have a video clip about totalmtd, totalqtd and TOTALYTD. I always have a problem with totalmtd and totalqtd. It appears that it is related to the date table is end on the 31st Dec 2021 while I am working on the mtd for Aug... Wonder how to solve the problem
Hey thanks for the explanation, how do the same with an id column instead of date as you did it with datesinperoid i want to have an average of the last 3 previous values using my id column that is unique in my table and is consecutive like an index column
Awesome video sir Mitchell, and I think if you do others videos about DAX where you use virtual Tables inside formulas it will be great because in DAX to take further steps after understanding evaluation context is to know how to create measure with using virtual tables. after all it's great what you do sir! thanks
Hi Mitchell, thanks for the nice Video, just was wondering how can we calculate the moving average for series of data in sequence of one minute or 5 minute, e.g. for a day have 1440 rows, appreciate your help.
One thing to note, that if in the expression for AVERAGEX you use expression (e.g. SUM('Sales'[Price]) ) it will not give you result you expect. It has to be a measure with exactly the same code (Total Sales = SUM('Sales'[Price]) ).
Very good observation! You are correct, X functions create a row context and since we are iterating over the date table we need to move that row filter into the filter context (Context Transition). Calling the calculated measure forces this transition to occur. We could write out the expression sum of sales amount but we would need to wrap it in a calculate statement like this: calculate(sum(salesamount)).
@@MitchellPearson thanks for clarification, I understand why that happens now. Had quite a bit of WTF moments while trying to understand why my measure was not working and was always equal to just total SUM.
Not sure if you guys gives a damn but if you guys are bored like me during the covid times you can watch all the new series on instaflixxer. Have been binge watching with my girlfriend for the last couple of months :)
This is the best Virtual table demo I've used and got me over the hump. I've been fighting this problem by burdening my model with helper tables in power query. This approach with DAX will level me up to take advantage of measures and the correct row context. THANK YOU!
Thank you Mitchell! The best video on TH-cam regarding the function.
Best Dax video I have ever watched bar none! You are a born educator. Bravo.
Many thanks for this! You have a talent in explaining complex things so well!! Please keep them coming!
Love your style Mitchell!! Thank you so much for these videos & great explanations. Hungry for more :). Greetings from Czech Republic / Europe. Martina
This is great feedback Martina, thank you so much :)
Thank you for another great video Mitchell. I am really fond of your teaching style, the way you lay it out all in a nice and simple way. It sure sticks to the brain. Looking forward to watching more of it.
Thanks Khardu, glad to help!
This is awesome! I've been looking at how to show month average by year for a bit and I can't understand how everything works together. You just explained everything clearly, not just the average but the use of multiple useful combinations that can be applied in complicated calculation. Thanks!!!
Thank you so much for the explanation! This is the best video I´ve ever watch about DAX, thank you again!!
Thank you Mitchell for such an awesome video. I have learnt a lot from your teachings at Pragmatic Works. Your explanations are very easy to understand. You can explain difficult topics very easily.
Thank you Hafiz, glad to help!
Looking forward to see future video regarding SUMMARIZECOLUMNS vs SUMMARIZE in DAX.
Thank you very much, you explain really easily and it's easy to follow you and put into practice. I cannot thank you enough!!!
The best when it comes to DAX, thanks Mitchell.
Such an amazing explanation!! Very very detailed! Loved it!!🎉
This was exactly what I needed, you’re great Mitchell!
Your explanations are always on point.
I'm follow you in pragmatic work !!! can't wait too subscribed this Chanel # love from Ethiopia
22' very well spent. Among other things, I appreciated the note on SUMMARIZECOLUMNS interaction of the context. Thanks Mitchell, I just subscribed your channel
Well done. Look forward to your other videos.
Thank you! Glad you enjoyed the video.
You're a life saver.. this is exactly what I needed for BI report.. thank you very much Kind Master 😊😊😊
Thanks for this, it helped me reason through a similar challenge I was facing.
That's awesome, glad it helped.
Amazing and fantastic explanation!
Thank you for the step by step explanation. Really helpful to understand the logic behind the solution, many thanks.
Great video and thanks for taking the time to explain everything in detail. I know this video is a couple years old. Is there a way to not return a 3 month average for the first 2 rows where there is not enough previous data to give 3 month average?
very well explained! I like the way you split the DAX formula into sections
Thank you Pawel, I'm glad you enjoyed it and found it helpful!
Excellent lesson Mitchell...not sure if you have gotten yet to the promised video on the difference between "summarize" and "summarizecolumns"
it would be great to get the files that you are using within your videos. according to me you are the one of the best in terms of teaching the background of the functions in daX
Great video and a really good teaching style, thanks!
Thank you so much. I was struggling with the same problem and you solve it.
Great breakdown, thanks so much! Easy to understand. Thanks again!
Thank you for the video! If there were no sales in some month, then how to calculate the average value correctly?
Helped me a lot. Thanks for the video!
This was a great tutorial! You really took time to go through every line of code & explain. Would you ever consider doing an average by week? Again thank you for posting this :)
Thanks for your great teaching
great explanation, really loved this:)
Excellent teaching style and awesome video.
Glad you liked it! Thanks!
great explanations ... thanks for your efforts to help us, to guide us, to teach us :)
Thanks. What instead of total sales, we had to count the number of tickets per month and then average them per year pls?
great tutorial. very useful... thanks.
Hi, I congratulate you for the really interesting topic. I would like to ask you a question. How can I apply this formula create with DAX with Excel Power Query that uses M language? Tnx for your reply!
Thanks Man. You made me understand filter and Row context!
Hey Mwangi, thanks for the feedback. I'm glad the video helped!
how would this work if one of your months were blank? would the averageX remove the null before the summarize?
I know I am 2 years behind 😂😂😂. But I want to see everything you touch, because everything you touch turns to golden knowledge. 😂😂 maybe co-pilot vs. Dax video
Good explanation , please share the practice dataset
Terrific video!!
The author is a genius
Talk for 20 minutes about what you can say in 5 minutes. This requires talent))
You made me laugh out loud. I am pretty sure this is not a complement, but I still like it :)
Hi @Mitchell, this is Raj from India. I need to achieve some scenario.
Let us assume 5 products were sold in the last month and 2 of them were sold in current month also along with
other products. Now, I need to exclude those 2 products and retrieve the count of last month sold products as 3 instead of 5.
eg: P1, P2, P3, P4 & P5 were sold in the last month. P1, P2, P6 & P7 were sold in current month.
Now I need to get the count of last month sold products but not this month as 3(only P3, P4 & P5 should be counted).
Please help me write the DAX measure code on this. Thanks in advance.
Hi, I know you mentioned values at the beginning, but is there any advantage to using Summarize?
I would just have used AVERAGEX( VALUES( Calendar [ MMM-YYYY] ) , [Total Sales] ) , I find Summarize a bit hard,
but have found it useful in reducing the rows of a table that need to be iterated over, so in a a total sales iterate over the sales table summarized by the dim product , customer etc.
MItchell... great video... Qucik question, how would do you obtain the rolling average? Do you change the daysinperiod to -12months? Thanks
How can we look at rolling averages over transactions instead of dates?
Hi Thanks for your videos. I would like to ask a question if I may. I would be interested in how RLS works with SSAS live connection and import mode. Is there a chance you can address this topic in a video?
best regards and stay safe
Excellent!
I would like to have a video clip about totalmtd, totalqtd and TOTALYTD. I always have a problem with totalmtd and totalqtd. It appears that it is related to the date table is end on the 31st Dec 2021 while I am working on the mtd for Aug... Wonder how to solve the problem
Can you make a video talking about the differences between SUMMARIZE() and GROUPBY()?
Its a gr8 video, why you are not making more videos
Hi Mitchell - Is there old videos have been deleted?
Hey thanks for the explanation, how do the same with an id column instead of date as you did it with datesinperoid i want to have an average of the last 3 previous values using my id column that is unique in my table and is consecutive like an index column
Awesome video sir Mitchell, and I think if you do others videos about DAX where you use virtual Tables inside formulas it will be great because in DAX to take further steps after understanding evaluation context
is to know how to create measure with using virtual tables.
after all it's great what you do sir! thanks
Thanks for watching and I'm glad you found this video helpful!
Please come back here.... where are you
Hi Mitchell, thanks for the nice Video, just was wondering how can we calculate the moving average for series of data in sequence of one minute or 5 minute, e.g. for a day have 1440 rows, appreciate your help.
Hi, how to get the PBIX file for this video? Thanks.
This tips is really well
Great Sir. Thanx a lot
One thing to note, that if in the expression for AVERAGEX you use expression (e.g. SUM('Sales'[Price]) ) it will not give you result you expect.
It has to be a measure with exactly the same code (Total Sales = SUM('Sales'[Price]) ).
Very good observation! You are correct, X functions create a row context and since we are iterating over the date table we need to move that row filter into the filter context (Context Transition). Calling the calculated measure forces this transition to occur. We could write out the expression sum of sales amount but we would need to wrap it in a calculate statement like this: calculate(sum(salesamount)).
@@MitchellPearson thanks for clarification, I understand why that happens now.
Had quite a bit of WTF moments while trying to understand why my measure was not working and was always equal to just total SUM.
You are the GOAT!!!
This was a great explanation 👏
Not sure if you guys gives a damn but if you guys are bored like me during the covid times you can watch all the new series on instaflixxer. Have been binge watching with my girlfriend for the last couple of months :)
@Allen Ray yea, been using InstaFlixxer for since december myself :)
how to calculate monthly average include months with no record, in a matrix at Year and quarter levels
Great video! Thxs ; )
Great demo...thank you
Thanks for watching!
You rock sir👍
Excellent,Thanks
Thank you too!
'X' functions are like SUMPRODUCT in Excel, if I am not wrong to put it simply.
Nice video as always.
I'm struggling with time and date functions. I'll email you a scenario if that's ok? What would be the best way to contact you
mpearson@pragmaticworks.com
Does not work for me.