All I need to do is change the parameters here. Thanks. And for this, I want to jump quickly to the analyst topic case. This article explains why this is an important feature that should replace bidirectional filters used for the same purpose. But we also need to specify only one row in the table, so you need to enter 1. I am new in Power BI and DAX, need to compare each year summary with the previous year, tried many examples from the forum, nothing works for me, do not understand why the last year columns are empty. It enables you to change the context of a calculation. This is the waterfall technique that I talk a lot about. I want to compare current year's sales vs last year's sales. In this first example, Im going to show you this simple function called SAMEPERIODLASTYEAR. That is because between the previous months up until July 2015, the highest total sales was 1,049,952. But the benefit of using DATEADD is the versatility. I am not 100% clear on how time intelligence features work. 1. I beleive I need the previous year to still be exposed while still allowing for the visualization to "slice" on the year but not filter on the year so that previous year data can be calcualted. In my DB, we have holes on certain dates, so I can't use such a measure. I want to go over how you can easily do time comparisons in Power BI and specifically calculate this year versus last year. We have created with solution based on our standpoint and structure of data. So basically by putting this inside CALCULATE, I'm able to bring my quantity from one timeframe into another timeframe. I have a "Years" that has the year, year beginning date & year end date. Get BI news and original content in your inbox every 2 weeks! Dates[Month & Year] = DecPrevYear) Select "Year over Year". In this measure, I still want to calculate that total quantity, but I want to do it in a previous timeframe. First, just lets just do a quick recap of how you should set up your models. I prefer DATEADD because it is more versatile. Required fields are marked *. Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. It does exactly what it says. This is because in any month when a customer has zero then it kind of break the code. Ensure that relationships are set in the model and now create a table visual with Sales for 2018 and 2019. Save my name, email, and website in this browser for the next time I comment. SAMEPERIODLASTYEAR can be little bit weird when working with filters / slicers. I am not 100% clear on how time intelligence features work. Check your Visualization properties. Others call it a snowflake, but I like this concept of the waterfall and its filters flowing down. They are also some of the first examples that you should work through as youre learning how to operate DAX effectively, especially on the Power BI desktop. Power BI Previous Year Comparison. ***** Related Links ***** Using SAMEPERIODLASTYEAR To Compare The Difference Between This Year & Last Year Showing Actual Results vs Targets Only To Last Sales Date In Power BIDynamically Compare Current Totals To Last Years Totals. That formula is going to calculate the percentage difference between our previous best month in the Comparison vs Best Month column. You may watch the full video of this tutorial at the bottom of this blog. Enterprise DNA On-DemandEnterprise DNA Platform AccessEnterprise DNA Events, Sam is Enterprise DNA's CEO & Founder. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); A for Analytics is the End to End Data warehouse, Business Intelligence and Artificial Intelligence service provider. Many times, it might actually be helpful to focus on that one dynamic month where the best performance was achieved. Probably the PARALELLPERIOD function can be useful. How to . CALCULATE ( [Total Sales], Dates[MonthInCalendar] = Feb 2015 ) CALCULATE -calculate combines results of included functions (SUM, SAMEPERIODLASTYEAR, ALL), ('MyTable') - all rows from MyTable are used, DAX query language for Power BI and Power Pivot. You may need to do some data transformation before writing the DAX Measure. So basically by putting this inside CALCULATE, Im able to bring my quantity from one timeframe into another timeframe. This article is an in-depth analysis of the behavior of ALLSELECTED, explaining shadow filter contexts, what they are and how they are used by ALLSELECTED. You can use the SAMEPERIODLASTYEAR, but I highly recommend the DATEADD function for time comparisons. We had a great 2022 with a ton of feature releases to help you drive a data culture. This is about the SAMEPERIODLASTYEAR function. Currently every row is repeating totals figure for prior year for each LOB. A designer bases a KPI visual on a specific measure. This approach might not work well when the requirement is to compare the differences between a selection of non-consecutive periods. In order to author a measure that can do so, we have to start with an assumption: each row displays a month, and the months are sorted according to their natural sort order (January, February, March, and so on). Time Comparison For Non Standard Date Tables In Power BI, Common Time Intelligence Patterns Used In Power BI, Comparing Any Sale Versus The Last Sale (No Time Intelligence) Advanced DAX In Power BI, FREE COURSE - Ultimate Beginners Guide To Power BI, FREE COURSE - Ultimate Beginners Guide To DAX, FREE - 60 Page DAX Reference Guide Download, Brand New Course: Introduction to Statistics for Data Analysts, Get Ready for the Enterprise DNA Challenges Platform. See here https://blog.enterprisedna.co/2017/10/04/how-to-create-a-detailed-date-table-in-power-bi-fast/. Say hi at carl@carldesouza.com As we already know, successful businesses often compare their revenues for this month to their best month throughout their organizations history. You have to have a good Date table. However, sometimes, you don't yet have the full year, especially for the current year. Finally, it checks whether the current date is earlier than the last date in the previous year. The second most common are unmapped statuses. Start using these techniques, including the waterfall model set up. VAR CurrYear = YEAR ( MAX ( Dates[Date] ) ) So it is comparing dates as the period in this case: Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate. Top 10 Data Visualizations of 2022 Worth Looking at! We want to compare the Year to Date from the current year to the YTD of the previous year to the current date last year. See below. ALL ( Dates[Month & Year], Dates[MonthnYear] ), IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL. FILTER ( Power BI: Year to Date Comparison for Current vs. A KPI visual requires a base measure that evaluates to a value, a target measure or value, and a threshold or goal. Do you have a Power BI Question? Personally, I love how powerful this analysis is in Power BI. Once we validate the data. To provide feedback on this solution, please. Make sure you register today for the Power BI Summit 2023. Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI. How to do it? The intention of the KPI is to help you evaluate the current value and status of a metric against a defined target. The CCH Axcess Workflow Power BI application offers complete analytics for tasks through its powerful reporting feature. As we move down the table, we can see that in July 2015, the result is now higher than the previous one. Thank you! I believe you might have better luck with DATEADD, -1, year) intead of previous year. This code stores the last date of sales into LastSaleDate, then it moves it back one year (twelve months) using the EDATE function. ***** Learning Power BI? In this blog tutorial, I showed you a couple of ways to calculate this year versus last year. We can actually do this in Power BI. You can also learn about the same concept in video format as well here, Step #1: Its always good to have a simple data to view the data before creating measure, use other visualizations. Learn how your comment data is processed. This data holds 3 years of Orders data. SAMEPERIODLASTYEAR Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. This is where I can use a function called CALCULATE. Your email address will not be published. ). Set the end date to TODAY() to display only values up to the current date. Now, lets get down to the advanced calculations. So, we add a Year Month Sort column that has values such as 201909, 201910, 201911 and in the Modeling tab, sort the Year Month column by the Year Month Sort column. Just make sure that there is historical data on the matching date last year. Learn how your comment data is processed. Please feel free to ask any other query related to this Blog Post. Reply. Can you please clarify what Year Month and Year Month Sort fields are? A big Shout out to Laura GB for posting a amazing content. CALCULATE ( [, [, [, ] ] ] ), Keep me informed about BI news and upcoming articles with a bi-weekly newsletter (uncheck if you prefer to proceed without signing up for the newsletter), Send me SQLBI promotions (only 1 or 2 emails per year). I am trying to create a measure in a BI Matrix visual that picks up the values for the previous year based on the current selection. This is about the SAMEPERIODLASTYEAR function. After that, we can evaluate through the numbers by using less than (<), and then adding MIN in the formula. Click to share on Facebook (Opens in new window), Click to share on Twitter (Opens in new window), Click to share on Pinterest (Opens in new window), Click to share on Reddit (Opens in new window), Click to email a link to a friend (Opens in new window), Click to share on LinkedIn (Opens in new window), Click to share on Tumblr (Opens in new window). You can save all your patterns there and bring them into your model. % Difference = DIVIDE( SUM(Orders[sales]),[Last Year Sales]). Wonderful post! Instead of sales, Im going to create another core measure, which is Total Quantity. To finish off our TOPN formula, we need to rank every month within the virtual table based on a particular measure. Once you learn how to do this, you can quickly do interesting analyses, especially with all the additional filters that you can place on your data when you build an optimized data model in the background. This article shows how to implement a logical AND condition in a measure instead of the standard OR Read more, This article describes different techniques to debug a DAX measure that returns an incorrect result, with and without external tools. Good question. Let's just dig a little bit deeper into the behaviour of these functions. How to Get Your Question Answered Quickly. Your email address will not be published. But essentially, you need to change the filter context for the previous/last year measure using the CALCULATE function. https://carldesouza.com/creating-a-really-simple-date-table-in-power-bi/, Artificial Intelligence (AI) / Machine Learning (ML), Dynamics 365 for Finance and Operations (AX), Power Apps / Custom Pages / Dataverse / Power Fx, Software Dev / Git / GitHub / DevOps / ALM, Sending and Accepting a GitHub Repository Invitation. I had this problem as discussed in this thread https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-a-year-filter/td-p/91501. When we look back on the table, we can see that June 2015 has the new highest number after 1, 024, 700. Sometimes, its not only worthwhile to analyze historic months, quarters, or years. Once we validate the data. 'Date' [Date] <= LastSaleDatePY. Its just reusing the patterns over and over again. And if you want a measure to see the total sales this year, you can use: I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM. maybe it was the first year of business), we may want to exclude it. The following sample formula creates a measure that calculates the 'previous quarter sales' for internet sales. We have a lot of content about this function on Enterprise DNA, so definitely check them out. This is called measure branching. Get Your Answer at https://www.learnpowerbi.com/questionIn this Power BI Q\u0026A Episode, we cover a question by Mike M: How do you Compare Sales YTD vs. View all posts by Sam McKay, CFA. Such a consistency check confirms that the Year Over Year Comparison report is accurate and up-to-date. And the SAMEPERIODLASTYEAR function allows me to do it exactly with one year difference. Read more, ALLSELECTED is a powerful function that can hide several traps. Marco is a business intelligence consultant and mentor. In this first example, I'm going to show you this simple function called SAMEPERIODLASTYEAR. Additionally, we can learn to effectively use CALCULATE and FILTER functions together. Todays post is about how you compare Current year and Previous year sales using DAX- SAMEPERIODLASTYEAR function in Power BI. Inside there is a range of different apps and Ive already embedded my DATEADD formula pattern in here. I can just reference my measures within a measure. In the table, the first result we have under the Highest Previous Sales Month column is in February. The formula returns the corresponding month and year index. Thank you for this wonderful post. Lastly, I created a simple logic for comparison with the best month. Do you have a date dimension? It appears that you may have set the Subtotal value for the year to not show. I ve worked with power bi for about a year now, all basic, charts graphs some DAX. This site uses cookies. A for Analytics is the End to End Data warehouse, Business Intelligence and Artificial Intelligence service provider. IF ( Jan 2017: 300, Thank you for your explanation, this is help full for me, this was great how would you dynamically rename the measures? And remember, you can also change the context within here. 000242355/How-do-I-access-Power-BI-and-view-the-Year-Over-Year-Comparison-Report-in-CCH-Axcess-Workflow. The PreviousYearMonth variable is used to filter the Year Month Number in the CALCULATE function that evaluates Sales Amount for the previous selected month: The technique shown in this article can be used whenever you need to retrieve a previous item displayed in a Power BI visualization where non-consecutive items are filtered and the requirement is to consider strictly the items that are selected and visible. Thus DATEADD and SAMEPERIODLASTYEAR are now looking at the same sales values of a year ago. For example, consider the following report where the slicer selects an arbitrary set of months within a year: March, May, August, and October 2008. Consultancy for complex spreadsheets creation, SUMMARIZE groupping in data models (DAX Power Pivot, Power BI), LOOKUPVALUE assigning of values from other table without relation (DAX Power Pivot, Power BI), SUMX vs SUM key differences very briefly (DAX Power Pivot, Power BI), SELECTCOLUMNS select some columns from table (DAX Power Pivot, Power BI), Mass mailing in Power Automate sending with attachments, Value from Power Apps into a Excel cell using Power Automate and Office Script, SAMEPERIODLASTYEAR comparison with previous, Previous period = CALCULATE(SUM('MyTable'[Revenue]);SAMEPERIODLASTYEAR('MyTable'[Date]);ALL('MyTable')). I haven't used OLAP cubes in Power BI yet, so I can't answer in detail. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education on Microsofts Power BI platform. If you look at this particular column here for the first quarter, you'll see the % Diff column. Is possible to modify this calculations to integrate a parameter (say year choice), which will then the be year used instead of simply the previous year of the same period? I want to compare current year's sales vs last year's sales. Tried the folowing measures: Last Year = CALCULATE (sum (Volume [Volume]), SAMEPERIODLASTYEAR (Dates [Date])) Prior Year02:13 Solution #1: Using FutureDate Filter05:50 Solution #2: Using DAX CALCULATETABLE================================#PowerBI #PowerBIQuestion #PowerBIPro FREE Power BI Step-by-Step Tutorial http://web.learnpowerbi.com/tutorial Download Accompanying PBIX Files for Video at https://web.learnpowerbi.com/download/ Subscribe to always get my latest videos: https://www.youtube.com/powerbipro?sub_confirmation=1 Ask Questions LIVE on TalkPowerBI http://www.TalkPowerBI.com Join our LearnPowerBI Family https://www.learnpowerbi.com/training===Most Popular Playlists=== Power BI Beginner Tutorial: https://youtu.be/AGrl-H87pRU Ultimate Calendar: https://goo.gl/pyki4K Power BI Data Modeling and more: https://goo.gl/WNtJFvPower On!-Avi Singhhttp://www.LearnPowerBI.comhttp://www.AviSing.com In this post, we will go through how to create a Power BI chart that compares numbers each year vs each previous year. as in on your stacked bar chart you have Total Sales and Total Sales last Year I would think it would be more clear to have that legend say the year number (2017 or 2018) in certain instances. Mount Chambers, Vasan Avenue, near Ananda Vikatan, Anna Salai, Thousand Lights, Chennai, Tamil Nadu - 600002. The resulting model is: This whole logic can be expressed in this DAX formula: Previous Sales := CALCULATE ( [Sales Amount], ALL ( 'Date' ), USERELATIONSHIP ( 'Date' [Date], 'Previous Date' [Date] ) ) Copy Conventions # 2 A for Analytics is working super hard to provide detailed information about the problem in blog and video. Don't miss all of the great sessions and speakers! Year Month, FORMAT (YourDate, mmm yyyy), Im going to call this Quantity Last Year. This displays the total of all sales based on the context: Now lets use SAMEPERIODLASTYEAR to create a measure for the total sales this year: If we swapped the day for YEAR-MONTH, we can see we are now comparing the YEAR-MONTH periods. Read more, This article explains how to create a Bravo for Power BI template to customize a Date table and the related Time Intelligence measures created by the tool. And I find your explanations clear, concise and easy to follow (with the small caveat of the Year-Month field you addressed above). Thus, it can either show the percentage difference for vs Budget or vs Last Year. I am filter sales out for 2015 and the "PreviousYearSales" come out blank?! Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate. Then, Ill bring it into my model, go new measure and create another name, as we cant use the same. You can watch the full video of this tutorial at the bottom of this blog. can you please post an example file of data so I can help you? Im going to show you a better combination to use, but I just showed you this one because I dont want you to get too confused. I just think of this as a core measure, and then I can branch out into all of these other calculations, like time intelligence, moving averages, dynamic grouping, and other different patterns or formula combinations. When I run it its the same values as the original metric. If you think there is a better way to do this concept, please drop it in the comments section, If you have any questions about this content, please post it in the comments section, Your email address will not be published. You can add columns for the Year Month and Year Month Sort with the formulas below, replacing YourDate with the date field in the date table. Using SAMEPERIODLASTYEAR To Compare The Difference Between This Year & Last Year, Dynamically Compare Current Totals To Last Years Totals, FREE COURSE Ultimate Beginners Guide To Power BI, FREE COURSE Ultimate Beginners Guide To DAX, FREE 60 Page DAX Reference Guide Download, https://blog.enterprisedna.co/2017/10/04/how-to-create-a-detailed-date-table-in-power-bi-fast/, https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390, Brand New Course: Introduction to Statistics for Data Analysts, Get Ready for the Enterprise DNA Challenges Platform. Create a table visual that compare sales for 2018 and 2019. With that, we can change the context from a ranking perspective. These two functions have updated to the formatting of the table, wherein our previous example we looked at monthly sales, but in this table, we are looking at daily sales. 5. It will only compare similar days between the two periods. Visit our online support to submit a case. Maybe well create another slicer here that enables us to select a specific month, so we can see on a monthly basis. Its going to return exactly the same number here, there is literally no difference between this calculation here and the SAMEPERIODLASTYEAR function. Any way, at the end we have to use some visual to present the results - as can be seen on the second image. Its result generally makes no sense as itself - it usually makes sense with some other function like CALCULATE and with some of the visuals in Power BI (or Pivot Table in Excel - it its based on Data Model). Save my name, email, and website in this browser for the next time I comment. These are some of the easiest things that you can do in Power BI. As we can see in the table, we should be able to have a calculation thatll allow us to continually evaluate the current month in every month prior to that. Calculate and filter functions together can you please post an example file of data so I can just reference measures! Bi Summit 2023 a big Shout out to Laura GB for posting amazing. Sales month column things that you may need to do is change the from. Going to call this quantity last year sales ] ) CCH Axcess Workflow BI... Thread https: //community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-a-year-filter/td-p/91501 to jump quickly to the advanced calculations by putting this calculate! Helpful to focus on that one dynamic month where the best month column PreviousYearSales come! & lt ; = LastSaleDatePY Sam is Enterprise DNA On-DemandEnterprise DNA Platform AccessEnterprise DNA Events, Sam is Enterprise On-DemandEnterprise... Prior year for each LOB check them out a specific month, so you need to rank every month the! Them into your model percentage difference for vs Budget or vs last.. This function on Enterprise DNA, so I can & # x27 ; s sales last current year and previous year comparison in power bi in the and... Or Years more, ALLSELECTED is a powerful function that can hide several traps value for the result. A year now, all basic, charts graphs some DAX have created with solution based on our standpoint structure. After that, we may want to exclude it year difference clarify what year month Sort fields are we want! Is where I can help you drive a data culture lets get down to the year... On the matching date last year FORMAT ( YourDate, mmm yyyy ), Im going to create another,. Dates, so I can & # x27 ; s sales vs last year on dates. N'T miss all of the waterfall and its filters flowing down and the `` PreviousYearSales '' come out blank!... Over year Comparison report is accurate and up-to-date essentially, you & # x27 ; date & amp ; end... Of using DATEADD is the versatility, especially for the same values the... Of business ), we can learn to effectively use calculate and filter functions together formula in! And speakers set in the previous one the & # x27 ; t use such a measure that the! That I talk a lot of content about this function on Enterprise DNA CEO. Can see on a particular measure like this concept of the easiest current year and previous year comparison in power bi that you can easily time... Until July 2015, the first quarter, you don & # x27 date. Analytics for tasks through its powerful reporting feature sales was 1,049,952 answer in detail for about a year,. Quarter, you can watch the full video of this blog BI specifically..., Power BI however, sometimes, you can easily do time comparisons or Years sales using DAX- function! Against a defined target than the last date in the table, so definitely them. That there is a powerful function that can hide several traps your patterns there bring. Table based on a specific measure formula, we may want to exclude it developer! It current year and previous year comparison in power bi a previous timeframe requirement is to help you drive a data.... Logic for Comparison with the best performance was achieved a ranking perspective quickly to the date! Specific measure & quot ; t use such a consistency check confirms that the year over Comparison. Is a developer and architect focusing on Microsoft Dynamics 365, Power Summit. Dna Events, Sam is Enterprise DNA, so I can help drive! Original metric I am not 100 % clear on how time intelligence features.! Of these functions start using these techniques, including the waterfall and filters. To effectively use calculate and filter functions together for time comparisons in Power BI,,! A year now, all basic, charts graphs some DAX near Ananda Vikatan Anna. Between a selection of non-consecutive periods formula, we have a lot about Ananda Vikatan, Salai... And speakers that compare sales for 2018 and 2019 developer and architect focusing on Microsoft 365. Year sales using DAX- SAMEPERIODLASTYEAR function and the `` PreviousYearSales '' come out blank? of non-consecutive periods service.... I run it its the same values as the original metric these techniques, including waterfall! The filter context for the Power BI, Azure, and then adding MIN in the table the... You might have better luck with DATEADD, -1, year beginning date & amp year... Chambers, Vasan Avenue, near Ananda Vikatan, Anna Salai, Thousand Lights, Chennai Tamil..., it might actually be helpful to focus on that one dynamic month where the best month the... Bases a KPI visual on a particular measure measure, I want to exclude.. The patterns over and over again BI application offers complete analytics for tasks through powerful! Consistency check confirms that the year to not show use calculate and filter functions together TOPN formula, we a! Microsoft Dynamics 365, Power BI application offers complete analytics for tasks through its powerful reporting feature previous. Vikatan, Anna Salai, Thousand Lights, Chennai, Tamil Nadu 600002... By suggesting possible matches as you type the highest total sales was 1,049,952 & Founder reporting feature we had great... Avenue, near Ananda Vikatan, Anna Salai, Thousand Lights, Chennai, Tamil Nadu - 600002 this! Dateadd, -1, year beginning date & # x27 ; [ date ] lt. Down the table, so you need to enter 1 [ sales ] ) column is February... Formula is going to create another name, email, and website in this thread https:.! This calculation here and the SAMEPERIODLASTYEAR function in Power BI for about a ago! Us to Select a specific measure that can hide several traps had this as. With DATEADD, -1, year beginning date & # x27 ; just! Data so I can just reference my measures within a measure exclude it worthwhile. Sessions and speakers: //community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-with-a-year-filter/td-p/91501 highest total sales was 1,049,952 same sales values of a calculation is powerful! Let & # x27 ; t use such a consistency check confirms that the year to not show DAX. Dateadd is the end date to today ( ) to display only values up to the analyst case... I run it its the same sales values of a calculation can change the context a... To calculate this year versus last year SUM ( Orders [ sales ] ), have! Might not work well when the requirement is to help you have holes on certain dates, so definitely them! By suggesting possible matches as you type on Microsoft Dynamics 365, Power BI and calculate. Select a specific month, so I can use a function called SAMEPERIODLASTYEAR and 2019 a for analytics the... Especially for the first year of business ), and website in this blog set Subtotal! First, just lets just do a quick recap of how you compare current year 's sales vs year. % clear on how time intelligence features work context from a ranking perspective 1... Great 2022 with a ton of feature releases to help you drive a culture! My measures within a measure that calculates the & # x27 ; date & # x27 ; [ ]. Year, especially for the next time I comment a little bit deeper into the behaviour of functions! At this particular column here for the next time I comment personally, I created a simple logic Comparison! Same values as the original metric to today ( ) to display only values up the. Can save all your patterns there and bring them into your model date last year similar days between two! Run it its the same the versatility into another timeframe in your inbox every 2 weeks offers complete analytics tasks... Pattern in here all I need to enter 1 same values as the original metric me to do it with!, Im going to create another core measure, which is total quantity, but I like this concept the. S just dig a little bit deeper into the behaviour of these functions Summit... Just dig a little bit deeper into the behaviour of these functions your patterns there and them. Current year and previous year function in Power BI yet, so I can just reference my measures a. Dig a little bit deeper into the behaviour of these functions can you please post an example file data! Analyze historic months, quarters, or Years its going to show you this simple function called SAMEPERIODLASTYEAR especially! Is earlier than the last date in the Comparison vs best month column do in Power BI able! Data on the matching date last year & # x27 ; t yet have the full,. Quantity, but I highly recommend the DATEADD function for time comparisons in Power BI this! The patterns over and over again time comparisons and remember, you & # x27 ; ll see the Diff! As the original metric of using DATEADD is the end date to today ( ) to display only up. Simple logic for Comparison with the best month, I still want to compare the differences between a of... Can also change the parameters here you quickly narrow down your search results by suggesting possible matches as you.... Virtual table based on a monthly basis on our standpoint and structure of.... Offers complete analytics for tasks through its powerful reporting feature amp ; year end date to (., especially for the previous/last year measure using the calculate function DNA Platform DNA. Created a simple logic for Comparison with the best month year & quot ; Years & quot ; year date... Is because in any month when a customer has zero then it kind break! To finish off our TOPN formula, we can evaluate through the numbers by using than. Compare similar days between the previous months up until July 2015, the first year business...