Look at data for every year but for current year, only through May

All replies

• 

  

  0

  Sign in to vote

  That's because your measure removes filters from Date and returns value for all dates inside DATESBETWEEN.

  This is enough but...

  Invoices per Claim Status Paid through May:=[Count of Invoices Status Paid]/[Claims Count Paid Invoice Status]
  

  ... to properly filter it in your pivot you should use a  column like this:

  Year_month

  ...

  2013/01

  2013/02

  2013/03

  2013/04

  2013/05

  2013/06

  2013/07

  2013/08

  2013/09

  2013/10

  2013/11

  2013/12

  2014/01

  2014/02

  2014/03

  2014/04

  2014/05

  2014/06

  2014/07

  2014/08

  2014/09

  ...

  
  

  
  

  Tuesday, September 13, 2016 1:15 PM
• 

  

  0

  Sign in to vote

           

  Hi AlexMartini,
  
  I am trying to reproduce your scenario in my sample data and get the expected results as follows.
  First,  your base calculation is [Sales] (EG: SUM(table[sales])), calculating the total sales over the last 10 months period selected is
  
  

   SaleInperiod:=CALCULATE(SUM(Cost[sale]),DATESINPERIOD(Table2[date],MIN(Table2[date]),1*5,MONTH))

  
  The same period(the first five month) calculation one in prior year is 
   

  SalesInPeriodLastYear:=CALCULATE([SaleInperiod],SAMEPERIODLASTYEAR(Cost[date]))

  
  

   Compare periods fist five month from my last date in current year to prior year is
  
  

   comparison:=[SaleInperiod]/[SalesInPeriodLastYear]

  
  

  Then, you create a pivot table as follows, regard the year as rows, SaleInperiod , SalesInPeriodLastYear and comparison as Values.
  
  
  
  In addition, you can use DATESBETWEEN function as below formula.
  

  First five month:=CALCULATE(sum(Cost[sale]),DATESBETWEEN(Table2[date],MIN(Table2[date]), DATEADD(Table2[date],5,MONTH)))

  
  

  For reference:

  https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

  http://www.powerpivotpro.com/2010/07/use-time-intelligence-functions-to-do-a-running-sum-of-the-last-6-months-with-powerpivot/.

  If this is not what you what, please give us more details and sample data structure.

  Regards,
  Angelia

  
  

  • Edited by Angelia ZhangMicrosoft contingent staff Tuesday, September 20, 2016 10:28 AM
  • Proposed as answer by Angelia ZhangMicrosoft contingent staff Thursday, September 22, 2016 10:05 AM
  • Marked as answer by Charlie Liao Thursday, September 22, 2016 10:19 AM

  Tuesday, September 20, 2016 10:05 AM