Calculate YTD last year up to current date

[MikeEcho]

I really hope someone can help me

My Table:
=========================================
                   NetInv_TY       NetInv_LY
Month        ThisYear          LastYear
=========================================
Mar 2017        100.00        130.00
Apr 2017         150.00        120.00
May 2017        560.00        380.00
Jun 2017          250.00        150.00
Jul  2017          180.00        250.00
Aug 2017        280.00         150.00
Sept 2017                           160.00
Oct 2017                            160.00
Nov 2017                           120.00
Dec 2017                           120.00
Jan 2018                            160.00
Feb 2018                            140.00
=========================================
Totals          1520.00        2040.00
=========================================

Assuming that the current date is 30 Aug 2017.
I need to calculate the YTD from the start of the FIN year, last year, up to the current date for the same period last year.
So in other words from Mar 2016 up to (current date, last year) Aug 2016.

The full related calendar table with all dates is 'Calendar', and the date column from the sales table is 'drsledger'[dbdate]
There is a slicer in effect for the period 20170301 - 20180228

I have been unable to find a working solution, I tried:

YTD NetInv LY =
VAR maxDate =
  DATEADD(LASTDATE('Calendar'[Date]),-1,YEAR)
VAR minDate =
  DATEADD(FIRSTDATE('Calendar'[Date]),-1,YEAR)
RETURN
 
CALCULATE (
    [NetInv_TY],
    FILTER (
      ALL('Calendar'[Date]),    
      AND( 'Calendar'[Date] <= maxDate,
           'Calendar'[Date] >= minDate
         )
    )
)

,but this just gives me the full period last year YTD total of YTD NetInv LY = 2040.00
I should be getting 1180.00

I think my issue might be with the maxDate and minDate, but when i try to workout the
current date, in current period, and use that as the maxDate, it just gives me an error.
I'm sure I am obviously calculate this maxDate incorrectly, the minDate seems to be fine.

Any ideas as to how I can do it?

[cwoo]

I am  also  having this problem .  It appears the  max date  is being  set at the  last month of last year month 12 ,  not  August as required.  

Microsoft BI needs to develop as modified  sameperiodlastyear ()  finction  since this is  adding up 12 month of last  year , when it only need to add up the  the months in the current year  that may possibly be  less than 12 months .

Colin 

[Ashish_Mathur]

Hi @cwoo,

Share the dataset and also show the expected result.

[cwoo]

Please explain  how i can attach the data set 

[Ashish_Mathur]

Hi,

Upload your file to Google Drive and share the download link here.

[cwoo]

https://onedrive.live.com/edit.aspx?cid=8e78c901484b76c2&page=view&resid=8E78C901484B76C2!71469&parI...

attached is  link for data file.

the financial year is april 30th .  

I am trying  to compare sales YTD may 2017 - sept 2017 (5 months)   in the financial year  2018  to sales   ytd may16  - sept 2016 (5 months)in financial year 2017 .

the problem is calculating  sales  may 2016  - sept 2016 ( last year ) (5 months) .  Since the  current  year  is not a full year it is only 5 months.

I created a measure to calculate  YTD  last year  = CALCULATE (sum( sales); SAMEPERIODLASTYEAR(date) ) however this calculating  may 2016 - april 2017 (12 months) .  Not  5 months (may -sept 2016). 

this is basic you comparing YTD  for this year to last year .  Why  is this so diffiicult  for Microsoft BI .  Tableau does this with ease from a drop down  menu.

Colin

[Ashish_Mathur]

Hi,

That link is invalid.  I get this message - "Excel This item might not exist or is no longer available - OneDrive This item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information."

[cwoo]

i will try  again . i  am the owner.  i went to one drive open the excel file that  was uploaded and copied the link below.

https://onedrive.live.com/view.aspx?resid=8E78C901484B76C2!71469&ithint=file%2cxlsx&app=Excel&authke...

Colin

[Ashish_Mathur]

Hi @cwoo,

You may refer to my solution here.

Hope this helps.

[cwoo]

can i just email it to you what is your email 

[cwoo]

How do you attached the data set ?

For  st augustine  store  , the financial  year  end  is  30th April   and looking to  compare   may - sept  2017 (5 months)  to  may - sept  2016 ( 5 months) so that will be comparing  financial  year  2018  vs  2017  .  Please note we only have sales data up to  sept  2017 .  

The problem  is  when   i try to calculate the  may - sept  2016  YTD last  year sales  it is  adding all sales for the financial  year  may 2016-  april  2017( 12 months )  .   i used calcualte YTD  filcal  year  last year Calculate( total sales ; SAMEPERIODLASTYEAR ( date )).  If i  only want to calculate the YTD last year  for the  may - sep2016   ( 5 months)  what  do you do ?  I tried a filter  with the  DATEADD function  with a max  and min  but this  did not work . 

COLIN

[Anonymous]

Give it a try... This should work...

Total Sales = SUM(Sales[SALE])

Total Sales YTD = TOTALYTD(Sales[Total Sales],'DATE'[DATE])

Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]))

[vcastello]

Hi MikeEcho,

Try changing the filter from ALL('Calendar'[Date]) to ALL('Calendar')

I think that should work

Vicente

[MikeEcho]

I tried that, but it made no difference, still getting 2040.00

[vcastello]

 and What are you trying to achieve.? I thought 1180 was the correct answerWhat are you trying to achieve.? I thought 1180 was the correct answer and you were getting 2040

[MikeEcho]

Oops, sorry, my bad, typo
I corrected my reply it's giving same answer as before 2040.00

[vcastello]

I see ...

Try changing the maxDate and minDate variables to 

DATEADD(LASTDATE('drsledger'[dbdate]),-1,YEAR) and DATEADD(FIRSTDATE('drsledger'[dbdate]),-1,YEAR) 

If you do, the measure should filter from the first date in the sales table to the last, depending on the filter you have placed in the slicer, because the measure is defined that way.

Now, it is filtering from the first to the last date of the one you hace in the calendar table.

It should work ...

[MikeEcho]

Hi vcastello, yea, I already tried that.
Problem is that those dates contain duplicate dates, so when I try the DATEADD I get
"A date column containing duplicate dates was specified in the call to function DATEADD"