9701

Compare totals for the same partial date range year-over-year in DAX / Power BI

Question:

I'm trying to create a table which shows a sum of monthly values for one year compared to the last year's totals (structured as the screenshot below): <a href="https://i.stack.imgur.com/PkZDw.png" rel="nofollow">Monthly Comparison</a>

However, the caveat I'm dealing with is comparing the most current month, which will always contain partial month data (unless it's the last day of the month), to the same date range of the previous year. In the screenshot I attached, our data for January 2018 only goes through January 22nd. However, it's comparing it to the <em>full</em> month of January from 2017, whereas we want that total to be January 1st - 22nd, 2017: <a href="https://i.stack.imgur.com/LUbTB.png" rel="nofollow">Value That Needs to be Updated</a>.

I've tried messing around with various MTD and cumulative totals, but I can't seem to get the logic to work while keeping the aggregation to the monthly level. Any idea what type of logic needs to used in order to compare year-over-year totals, but only do a partial sum for the same date range of a month that is currently in progress?

Thanks in advance.

Answer1:

In my short example, this seems to work:

Total Sales LY 2 = VAR MaxDate = EDATE(CALCULATE(MAX(Sales[Date]);ALL(Sales));-12) RETURN CALCULATE( [Total Sales]; FILTER(SAMEPERIODLASTYEAR('Date'[Date]);'Date'[Date]<=MaxDate) )

I calculate Total Sales for the same period last year, with the max of the last available sales date this year.

<ul><li>Total Sales LY = Comparing last year full month (wrong)</li> <li>Total Sales LY 2 = Comparing last year month, with max of last sales date</li> </ul>

<a href="https://i.stack.imgur.com/cuuqR.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/cuuqR.png" data-original="https://i.stack.imgur.com/cuuqR.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

<a href="https://www.dropbox.com/s/kgsh02xy2f0lvb0/DAX_31-3-2018_2129.pbix?dl=0" rel="nofollow">PBIX file</a>

Recommend

  • Set User Control's default event
  • jQuery ajax security
  • Combining many rectangles into fewer rectangles
  • How to get list of users who's birthday is today in MongoDB
  • MySql - get days remaining
  • How to use arithmetic operators with SAS macro variables [duplicate]
  • How to add System.Windows dll to Visual Studio 2010 express?
  • How to work with Master Page that is attached to the page via the page's basepage?
  • Implementing Oracle.Web.dll, Oracle.DataAccess.dll 64-bit failed on Windows Server 2008 with IIS 7.5
  • Fire callback when selection was made with select2 4.0, and retrieve the value of last selection
  • Returning this from a constructor function in JS
  • Azure table query partial partitionkey guid match
  • react split panel resize
  • What is the use of a session store?
  • How to get the date of next specified day of week
  • jQuery: add elements until a particular height is reached
  • Find Previous month name using Calender or any classes that in java
  • Implementing “partial void” in VB
  • Invalid Date on validation Date of js
  • How to revert to previous XCode version?
  • Appending Character to Character Array In C
  • Excel's Macro-Recorder usage
  • Ionic 2 storage is not cleaning up on uninstall - Only for signed APK
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Illegal mix of collations for operation for date/time comparison
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • How to handle AllServersUnavailable Exception
  • Updated Ionic CLI but shows previous version (Windows)
  • Release, debug version and Authorization Google?
  • VBA Convert delimiter text file to Excel
  • How to get next/previous record number?
  • Confusion with PayPal's monthly billing cycle
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Hits per day in Google Big Query
  • embed rChart in Markdown
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Reading document lines to the user (python)
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • Conditional In-Line CSS for IE and Others?