Calculate Difference This Year vs Last Year in Power BI with DAX

Подписаться 93 тыс.

Просмотров 283 тыс.

50% 1

Check out the 2022 update of this video here: • Calculate Difference T...
Learn how to use DAX measures to work out the difference between calculated amounts this year versus last year. Get an intro into the concept of measure branching in DAX.
**** Video Details ****
0:24 - summing up revenue column
0:37 - total sales measure
1:46 - reviewing total sales information by date
2:34 - total sales last year
4:20 - difference between sales this year vs last year
5:42 - visualization of difference between sales this year vs last year
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - Power BI Resources - enterprisedna.c...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
Learn more about Enterprise DNA - www.enterprised...
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Events - enterprisedna....
**** Related Links****
Calculating Percent Profit Margins Using DAX in Power BI - blog.enterpris...
Calculate Financial Year To Date (FYTD) Sales In Power BI using DAX - blog.enterpris...
Calculate Consecutive Weekday & Weekend Days In Power BI Using DAX - blog.enterpris...
**** Related Course Modules****
Time Intelligence Calculations - portal.enterpri...
Mastering DAX Calculations - portal.enterpri...
DAX Formula Deep Dives - portal.enterpri...
**** Related Support Forum Posts****
SamePeriodLastYear returns this year selected period value - forum.enterpri...
Grand Total not working properly - forum.enterpri...
Financial templates vs LastYear make sameperiod - forum.enterpri...
For more SAMEPERIODLASTYEAR support queries to review see here - forum.enterpri...

Опубликовано:

10 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 114

@EnterpriseDNA 5 лет назад

***** Related Links***** Calculating Percent Profit Margins Using DAX in Power BI - blog.enterprisedna.co/2019/01/14/how-to-calculate-profit-margins-in-power-bi-wdax/ Calculate Financial Year To Date (FYTD) Sales In Power BI using DAX - blog.enterprisedna.co/2018/12/10/learn-how-to-calculate-sales-financial-year-to-date/ Calculate Consecutive Weekday & Weekend Days In Power BI Using DAX - blog.enterprisedna.co/2018/10/31/calculate-consecutive-weekday-weekend-days-in-power-bi-using-dax/ ***** Related Course Modules***** Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives ***** Related Support Forum Posts***** SamePeriodLastYear returns this year selected period value - forum.enterprisedna.co/t/sameperiodlastyear-returns-this-year-selecte-period-value/1862 Grand Total not working properly - forum.enterprisedna.co/t/grand-total-not-working-properly/2311/2 Financial templates vs LastYear make sameperiod - forum.enterprisedna.co/t/financial-templates-vslastyear-make-sameperiod/2214/2 For more SAMEPERIODLASTYEAR support queries to review see here - forum.enterprisedna.co/search?q=sameperiodlastyear

@RichArd-ee2qj 6 лет назад

Your an excellent teacher! Voice control. Organized thoughts. You don't go 'ahhh' (which is what I often do when I speak). You rock!

@excaliber4ps 5 лет назад

He is my Power BI sensei!!!!! Masterclass of his own. I cannot tell you how much your vid’s have helped me. Please keep it up mate!

@bosnianinny 3 года назад

Excellent step by step approach! Saved me a lot of time trying to write short DAX script. Thanks a mill.

@EnterpriseDNA 3 года назад

Hi Adis Hodžić, glad you appreciated the video. We are so grateful to have had such positive feedback.

@AmeyInamdar 6 лет назад

Thanks for the Video! (Heads up- it will work only when you've got contiguous set of dates)

@EnterpriseDNA 6 лет назад

Only use a date table for any and all time intelligence calculations, which are and would always be continuous

@mrgreenteatime2987 5 лет назад

Good formula, however what about Totals? This current method would not calculate totals correctly

@romasejal 4 года назад

I have calculated same period last yr as per ur video but when i am selecting the year for which i want to make chart the same measure shows blank result. Why the same is happening? Pls guide

@MrErolyucel 6 лет назад

Sam, your instructions saved my day

@geubes 5 лет назад

Very useful, my Date data was raw so first grouping into Bins based on Month and using that in the filter has given me the perfect graph of the last six months period totals Year on Year

@nancmadi 3 года назад

I absolutely LOVE your videos, they are so clear and very understood. A great monotone and easy to listen to and follow along so nicely. THANK YOU for another wrinkle in my brain.

@EnterpriseDNA 3 года назад

Hi Nanc, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@curiouspie1264 2 года назад

Thanks so much for your M Power Query and Dax time intelligence series. This is great. 🙌

@EnterpriseDNA 2 года назад

Hi! Glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

@tlee7028 2 года назад

Thank you! This helps lot! 😀

@EnterpriseDNA 2 года назад

Hi, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

@likandoluywa3973 4 года назад

Viewed several videos this was simple and best for Yr by Yr calculations Power BI sensei!!!!! You have done wonders and helped me. Please keep it up mate!

@mdarefbillah4784 3 года назад

This tutorial was very useful. Thanks for your effort.

@EnterpriseDNA 3 года назад

Hi Md Aref Billah, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@decentmendreams 5 лет назад

Oh, I am so indebted for this video . I work in the hotel industry and half of my time I spend comparing YOY(Year Over Year) variances. This is. Eureka moment for me . Thank you very much . I have a Question. How would I go about calculating a revenue /room nights change from the last time I run my numbers . I know you are busy but thought I would give it a shot. Thank you again,

@EnterpriseDNA 5 лет назад

Hi Micael. for help like this check out the Enterprise DNA Support Forum - forum.enterprisedna.co/

@jensmlgaardsrensen5386 3 года назад

You are the king of cool! Worked like a charm first time! You are a fantastic teacher! :-)

@Magmatic91 Год назад

Can someone please help me understand why sometimes I see people adding a date table that automatically generates dates ranging from min and max date in the data and then link this table to the data model? Thanks.

@lianchi2646 5 лет назад

Thank you for the tutorial Sam! It saved my day! Just out of curiosity, I tried not to create a separate date mapping table and then this method didn't work. To make this work, you have to have a Date mapping table.

@YabaluriGoutham 5 лет назад

very clear and useful !, thumbs up !

@rayanthonycleofe5243 2 года назад

This is so helpful

@EnterpriseDNA 2 года назад

Hi Ray Anthony, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

@KarineLago 7 лет назад

Great lesson, Sam! Thanks!

@aliazad1118 4 года назад

Perfect thanks a lot

@james_msuk Год назад

How would you do this if trying to compare 2019 to 2023?

@EnterpriseDNA Год назад

Hello @user-xj7gy9xp3w, We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA RU-vid channel pertaining to this topic. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our RU-vid channel and LinkedIn group to keep posted on the latest data skills and tools updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/ blog.enterprisedna.co/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi/ blog.enterprisedna.co/power-bi-time-functions-dateadd-and-more/ blog.enterprisedna.co/power-bi-dateadd-function-time-related-insights/ ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-N-77CnEWe0I.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pioJAenHEMg.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-1P99rg9a6es.html

@felipesignorellireis7839 5 лет назад

Great Video. Thanks

@aliibrahim171 4 года назад

Plotting the shown table values doesn't work on column line chart. WHY?

@FalconFlyer75 3 года назад

do you have anything for previous Weekday? for example lets say I want to compare the sales on Thusday December 10th 2020 to Thursday December 12th 2020 so comparing the weekday (mathematically all I need to do is subtract 364 days from the Date to get the prior year date) but I can't find anything in DAX that would allow me to do this

@jazzista1967 7 лет назад

Great video and excellent explanation. Thanks

3 года назад

Great. Except for you lost me with the calendars at the end. Do you happen to have a link that would help me to know what those date calendars have to look like ?

@ameerbisyeed6452 Год назад

Current year sales & last year sales comparison product category wise sales percentage how

@RobertSmith-pf8ox 3 года назад

Dear Sam, thank you for the video. I use Year slicer with selected 2020 year. Could you show how to change Total Sales LY measure please. With selected 2020 the measure does not calculate Total Sales LY for 2019. Best regards

@EnterpriseDNA 3 года назад

Hi Robert Smith, thanks for posting your comment here in the video. This topic has been discussed several times in Enterprise DNA Forum. You might get the answers you are looking for. If you want to check it out here is the link: forum.enterprisedna.co/search?q=Total%20Sales%20Last%20Year%20measure

@RobertSmith-pf8ox 3 года назад

It works. Thank you very much for your help.

@sujatanadkarni8386 5 лет назад

How @ YTD in Time, for instance CYYTD sales is Jan2019- Aug20th 2019, How is LY YTD calculated from Jan2018- Aug20th 2018 .... I have used SAmeperiodlastyear also parallel year ...it gives me the entire last year sales. ... please advise.

@Anatoli8888 5 лет назад

Hi Sam, do you know why these measures don't work from the date column from the source and you have to create a separate date table?

@Realsoul4769 4 года назад

When using Dax formula total sales is not showing in drop Down.

@RuleFox 3 года назад

Hi, what if you wanted to find the difference in a five year period? Say 2015-2020?

@EnterpriseDNA 3 года назад

Hello Francois Ra, thank you for posting your query onto our channel. Well you can also use the "DATEADD" function which provides wide range of granularity in terms of calculations i.e. DAY, MONTH, QUARTER and YEAR. In order to calculate the difference between this year and last 5 years. Below is the example of the formula provided for the reference. Totals Sales Last 5 Year = CALCULATE( [Total Sales] , DATEADD( Dates[Date] , -5 , YEAR ) ) We're also providing a link of a video pertaining to this function available onto our Enterprise DNA RU-vid channel for the reference. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pioJAenHEMg.html blog.enterprisedna.co/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi/ forum.enterprisedna.co/

@jaggedjunkie1 5 лет назад

Hi. I tried following this but i receive an error saying i need ‘contiguous dates’. Im using a matrix and my table has a slicer based on fiscal years. My total sales measure also uses a filter; not sure if this is causing a problem. Do you know howni can fix this?

@EnterpriseDNA 5 лет назад

Hi Daniel, for help like this check out the Enterprise DNA Support Forum -forum.enterprisedna.co/search?expanded=true&q=fiscal%20years

@neilgore Год назад

Super ... thanks

@EnterpriseDNA Год назад

Hi Neil, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

@juansantamaria1982g 2 года назад

Why sameperiodlastyear doesnt work with leap years? when february has 29 days it doesnt return data.

@EnterpriseDNA 2 года назад

Hello Issac, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post. Well, the reason here is pretty much starightforward. The current year is not a leap year and therefore it doesn't contain 29th Februray as a date in it. And this is not only a problem with the "SAMEPERIODLASTYEAR()" function but even if, you use "DATEADD()" function, it'll give the results as blank since it ignores that date but will include the results at a grand total level. In such cases, what you can actually do is create a result as a card which shows you the differential result as 29th Feb, just an idea to work on here. For furthermore queries, you can also reach out to us onto our Enterprise DNA Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Hoping you find this useful. Also, make sure that you're subscribed to our RU-vid channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ru-vid.com www.linkedin.com/groups/12004506/

@LouG77 3 года назад

why not use SUMX? just asking because I don't have a full grasp on that function

@EnterpriseDNA 3 года назад

Hello Luis Garcia, thank you for posting your query onto our channel. Well, you can also use the "SUMX()" function but in this case it was actually not required since we already had a column with a name "Total Revenue" in the Sales Table which will provide the same results as "SUMX()" function and therefore we just simply used the "SUM()" function. In order to use the "SUMX()" function just create a formula as provided below and you'll see that identical results are being generated. Total Sales = SUMX( Sales , Sales[Order Quantity] * Sales[Unit Price] ) We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA RU-vid channel pertaining to this topic which will further clarify and strengthen your understanding between the two functions. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our RU-vid channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA blog.enterprisedna.co/difference-between-sum-vs-sumx-in-power-bi/ forum.enterprisedna.co/t/sum-vs-sumx-whats-the-real-difference-between-these-dax-measures/5109 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-uaocmDf5oFU.html

@MichaelDoig 4 года назад

Hi there, I need to do sameperiodlastyear but line up the day of the week - ie Saturday must line up with Saturday. Even if it crosses month bounds. Handling normal one day offset and (leap year) two day offset. What do you recommend in DAX? Thanking you.

@EnterpriseDNA 4 года назад

Hi Michael, Please check Enterprise DNA Forum for this and lot other scenarios - forum.enterprisedna.co/ Regards, Enterprise DNA

@joshnezbeth1342 3 года назад

How could this be changed to compare today and yesterday values?

@EnterpriseDNA 3 года назад

Hello Josh Nezbeth, thank You for posting your query onto our channel. Below are the steps provided for the reference about how you can derive the suggested results into your analysis. A. Calculate the Today's Sales Results - Total Sales = SUM( Sales[Total Revenue] ) OR Total Sales = SUMX( Sales , Sales[Price] * Sales[Quantity] ) B. Calculate the Previous Day's Total Sales Results - Total Sales Previous Day = CALCULATE( [Total Sales] , DATEADD( Dates[Date] , -1 , DAY ) ) C. Difference Between Today's and Previous Day's Sales - Difference Between Sales = [Total Sales] - [Total Sales Previous Day] Note: The above formulas are provided for the example purpose. Please incorporate the formula along with the proper naming conventions into your file and you'll be able to derive the results accordingly. For furthermore queries, you can reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. We're also providing a link of the forum below for the reference. Along with that we also encourage you to please go through the course "Ultimate Beginners Guide To Power BI and DAX" which is absolutely available at free of cost onto our RU-vid channel as well as Education Portal. We're also providing a link below of it for the reference. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! portal.enterprisedna.co/courses/enrolled/157239 portal.enterprisedna.co/courses/enrolled/195681 forum.enterprisedna.co/

@neilessmyer9892 5 лет назад

Hi Sam great video as always. I was wondering if you've published anything specifically around Like for Like sales? Basically I'm trying to report on stores that have sales in the current period, and the prior which is usually YoY, while filtering out any stores that did not have sales in both.

@kevinstevenson2927 4 года назад

Hi, we use "W/C date" for each week. So if we use SAMEPERIODLASTYEAR it won't find any data. Suggestions?

@EnterpriseDNA 4 года назад

Hi Kevin, Be sure to make sure you have a good Date dimension table as this is of paramount importance for any meaningful time intelligence in Power BI. For help with custom calendars be sure checkout the videos below. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Rj5JbAYXHAg.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-aODroF37pq0.html For any specific queries be sure to checkout the Enterprise DNA forum. forum.enterprisedna.co/ Regards, Enterprise DNA

@delkaaaa 3 года назад

what if i want to compare months of different years? january 2005 - to january 2010 for example

@EnterpriseDNA 3 года назад

Hello delkaaaa, thank you for posting your query onto our channel. Well, in that case, you can make use of the "DATEADD()" function which is much more diversified function than the "SAMEPERIODLASTYEAR()" function. "SAMEPERIODLASTYEAR()" function helps us to evaluate the results when we want results to be evaluated at a Yearly level whereas "DATEADD()" function will help us to evaluate the results at a Daily, Monthly, Quarterly and Yearly level. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA RU-vid channel pertaining to this topic. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our RU-vid channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA blog.enterprisedna.co/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi/ blog.enterprisedna.co/power-bi-time-functions-dateadd-and-more/ blog.enterprisedna.co/power-bi-dateadd-function-time-related-insights/ ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pioJAenHEMg.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-1P99rg9a6es.html

@delkaaaa 3 года назад

@@EnterpriseDNA thanks a lot!

@alanaly52 4 года назад

Hi! i have a question with the YoY. I would like to compare the YTD sales of the lines with the same combination on 2 columns "Customer" and "Product". Meaning, i want to exclude the lines with negative or 0 sales on either this year or last year. If i use the isblank function, it only works when i make a table with the customer and product listed line by line. But if i see the grant total, it still add all the lines without excluding the blank lines. How can i achieve that? My YOY comparison is not to filter out the blank sales by dates but to filter out the blank sales by customer and product. Thank you so much!!

@EnterpriseDNA 4 года назад

Hi YI LI, Very difficult to evaluate without looking at your model. Remember Context is of utmost importance in any Power Bi calculation. Your question suggests that you are trying to change the context of your calculation. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qiLwt3Pmxv8.html You should look at virtual tables in Power BI this could also help. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QiWkg0scJHo.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--pWvjcUTz_U.html Be sure to checkout the Enterprise DNA forum for any specific queries, you can post PBIX files so can be much easier to decipher. forum.enterprisedna.co/ Enterprise DNA

@Amaankhan-mg5xi 5 лет назад

how to deal with the error "expects a contiguous selection when the date column" with the function when there are more then one variables are involved.

@anarhell 4 года назад

Good day, is amazing , anyone know where download the dataset ?

@arashpreetkaur3811 4 года назад

Thank you! Can you please do a video on how to do a comparison of weekday to last year’s weekday for the same time period. I have tried everything and can’t find a solution. I would greatly appreciate if you can. Thank you!

@EnterpriseDNA 4 года назад

Hi Arashpreet, you may watch this tutorial on comparing data virtually: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-opYn_wDngMI.html You may likewise browse the contents in the support forum for related topic: forum.enterprisedna.co/ forum.enterprisedna.co/search?q=compare%20this%20year%20vs%20last%20year Thanks!

@davehussey4024 6 лет назад

Hi Sam, How do I get the totals column on a matrix to calculate YTD. I have a matrix and the Variance calculates to the end of the month last year. Any ideas how to overcome this?

@EnterpriseDNA 6 лет назад

Would have to see more as it could vary depending on the model you have. I provide more specific support at the Enterprise DNA Support Forum - forum.enterprisedna.co/

@laurensc1302 4 года назад

Hi, Thanks for the tutorial, but I might be missing something here. If I follow your method step by step, my totals don't add up. When I do this to my data (I just have monthly totals in my original data already) the totals of the colums ' Total LY' and 'Total sales' is not the same. Because the first column has data for both years and the second colum 'Total sales LY' only for the last year. How come yours have the same total? I believe this is the reason I can't limit my visuals to certain months, because it keeps going to that total which is huge because of the two year data vs only one year data comparison... Hope you can help me out! Thank you

@laurensc1302 4 года назад

I do have also forecast data in there so till dec 2020, ofcourse my table does not show 2021. Should I create a date table with data till dec 2021?

@EnterpriseDNA 4 года назад

Hi Laurens, Thank for your query. Once you use Date/Month in your visual "Total Sales" shall limit to that Date/Month only. It's not possible to suggest without looking at Data Model and the visuals. For additional information, you may search this topic in the Enterprise DNA Forum at forum.enterprisedna.co/ Enteprise DNA Team

@DanielWeikert 6 лет назад

Is there a good way to do an apples to apples comparison for running totals in certain ranges? e.g. We startet selling in Feb 2017 until jun 2018. Now I like to see the running total in comparison to last year but only feb17 to jun 17 vs. feb18 to jun18. Thanks

@EnterpriseDNA 6 лет назад

Sure you would just need to implement to correct time intelligence functions. Probably a combination of CALCULATE and DATEADD should enable this.

@giacomobianchi8 4 года назад

Thank you very much, this is great. I am still struggling to make the formula work. Does the calendar need to be in a specific formula? I do everything you say in the video but when I pull the Total Sales LY, nothing comes up... Thanks!

@EnterpriseDNA 4 года назад

Hi Giacomo, you need to create a Date Table and make relationship with Sales Table as shown in below Video. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KLfOOIAqfkQ.html Regards, Enterprise DNA

@yeasinarafatredoy9665 6 лет назад

Hi, Thanks for Your Lesson. But if i want to get the LMTD ( last month to date ) Vs MTD ( Month TO date) Data. How i will get the data.

@EnterpriseDNA 6 лет назад

Check out this tutorial - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pioJAenHEMg.html

@marshal115 7 лет назад

If the calendar table was created only for a monthly basis, say the end day of each month, how do you calculate monthly sales of leap year? Sameperiodlastyear function won't work for Feb of leap year right?

@EnterpriseDNA 7 лет назад

Yes on a daily perspective it would not. But if you had a entire month context then it would be fine as it would read Feb. As a recommendation I would ALWAYS have a full date table (including every date) instead of what you have now

@JorgeGrisman 6 лет назад

Hello Sam, this model is empty in the enterprise DNA TV resources, it does not have any content.

@EnterpriseDNA 6 лет назад

Sorry the measures are in the Sales table in the model. I will update it though

@danghuyen3163 5 лет назад

Could I have the data sample that you use, please?

@philipsaporito3421 7 лет назад

Great video! How would you go about doing this for week over week change?

@EnterpriseDNA 7 лет назад

A good understanding of CONTEXT is important in this case. Check out this video, slightly different to what you're asking but exactly the same concepts - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-hGdYR1KR3rc.html

@FouManiaTube 6 лет назад

Hi Sam, Thank you for that info, but i have a problem when i put those measures into one table it work when i use the DATE as "legend" but when i use MonthInCalendar or other it does not work anymore. For information i'm in DirectQuerry not in import mode (SQL Server source)

@EnterpriseDNA 6 лет назад

Not sure exactly. Measures don't all work in directquery, so that's one thing to be mindful of.

@FouManiaTube 6 лет назад

I've tried with import and i've the same problem, it's like it can't summarize data if they are not seen as the date level.

@FouManiaTube 6 лет назад

So i find the solution, i had to use a table with date (I had a my date and value in the same table so for this to work you need to have a specific table for date parts).

@jakob2746 6 лет назад

hi Sam (or anyone with this knowledge), How do i also hide the difference between TY and LY for those periods, which have no yet occured in the current year? So, if we are in 2018-07, it should show the total sales LY for the coming months until 2018-12, but it should be blank in the "diff. between..." column. Right now it just states some large minus, because no sales has happened yet.

@EnterpriseDNA 6 лет назад

Just use simpe IF Logic. Like so - IF( ISBLANK( TY..), BLANK(), LY )

@nehaganjoo 7 лет назад

It would be great if you could share the datasets, so we can try doing this for ourselves. Thank you so much.

@EnterpriseDNA 7 лет назад

Please see description. You can purchase them with a small investment

@slinktus 3 года назад

Great video thank you. I was wondering how I could do a this time last year comparison but it not be directly this time last year. e.g I dont want the comparison to show the figures for 01/02/2019 to 01/02/2020. The reason is, I want to compare school terms this year to last year. As the start of our school terms don't exactly line up each year the normal compare this time last year won't show the correct information. 01/02/2019 might actually line up with 05/02/2019.

@EnterpriseDNA 3 года назад

Hello Go Away, thank you for posting your query onto our channel. Well for this sort of queries, we encourage you to please write back to us onto our Community Forum by providing the description of your problem and attaching the working of the PBIX file for the reference where our members as well as experts team will be able to assist you in a better and efficient manner. Since it's always a little bit difficult to judge and provide the results without looking at the data. Below is the link of the forum provided for the reference. Hoping this helps you. Happy Learning!!! forum.enterprisedna.co/

@joanz4347 6 лет назад

How is leap year handles?

@moulik2341 7 лет назад

nice video my friend.can you explain how can we achieve LMTD, i.e. last month to till date in a measure ???thanks in advance.....

@EnterpriseDNA 7 лет назад

You want to use a combination of DATEADD and cumulative total calculation. If you search for both of those concepts on youtube you will find videos on Enterprise DNA TV that describe those techniques.

@MatthewNguyen90 5 лет назад

This is great! One question I did have, is it possible to put in your own custom comparative date mapping instead of the time intelligence function SAMEPERIODLASTYEAR ? for example, thanksgiving shifts a week, and I want to look at a different LY date.

@MatthewNguyen90 5 лет назад

For me to currently bypass this, i've created a mapping table, and then imported duplicate sales tables and created relationships on current date & comparison date. However, I would love to avoid that because bringing in a second table when you have 80 million records seemed excessive.

@EnterpriseDNA 5 лет назад

This will require some additional logic wrap in an IF statement or SWITCH statement I would say. There's a little bit to it and would probably need to test the scenario. For more personalized help check out here - forum.enterprisedna.co/

@Hemantshyontry 7 лет назад

Hi Sam, My sincere greetings. It's been a pleasure watching your videos. The way you teaches is simply astonishing. I need a small help from you,I am trying to create a measure wherein I would like to find top 10 performers in a heirarchy of branch-->distributor-->retailer. Various year are on slicer. I am able to create the measure for top 10 performers for the above mentioned heirarchy independently but not able to do so in a single visual with drill mode on. Can you please help??

@kauveryshivaji6195 5 лет назад

HI sam can i get the data source of this tutorial.

@EnterpriseDNA 5 лет назад

HiKauvery, all pbix files can be found in 'Mini Series' modules at Enterprise DNA Online - portal.enterprisedna.co/ or you can check out the Enterprise DNA Support Forum - forum.enterprisedna.co/