YTD and SAMEPERIODLAST YEAR ON SAME PAGE

[umpoohg]

Hello all,

Hopefully someone can help me with the issue I am experiencing while attempting to create a sales report using YTD and then also getting the same period last year.  I have tried so so many different options, based on solutions I have seen here.  However none of them seem to work as I expect.  Specifically, below I have a measure YTD Sales = TOTALYTD(SUM('IHeads'[Sales]),IHeads[DateKey]).  From the screenshots below, can someone help me understand why if I do not use a filter, there are still dates from years other than the current year?  Even weirder, it seems the total seems to be only using the values from this current year as expected, whether the filter is there or not.  

Is it possible to use the sameperiodlastyear function on this same report?  It really seems as I cannot using measure 

same period last year sameperiodlastyear = CALCULATE(SUM(IHeads[Sales]),SAMEPERIODLASTYEAR('DateTable'[Date]))

I have created a date table, but that does not have the 'date intelligence' I am expecting.

When I add that to the report, I get a total, but it does not show the details, just a total.  Maybe if someone can help me understand this behaviour, maybe I can move forward with the report.

[umpoohg]

I have been able to get this to work somewhat, but its not the same or parallel period...ugggg

Any assistance would be greatly appreciated.

[Peter_R]

You may want to take a closer look at your date table. Those time intelligence functions are quite finicky with respect to the date table - it needs to consist of a contiguous date range across all the dates in your fact table. I'm noticing that there is a blank value in your year slicer, which suggests that your date table may not have all the dates in it that are in the fact table. Also, I see one of your formulas refers to datekey, which may not be a date datatype. Preferable, I believe, to use the actual date value from the date table (as you are doing in other places).

[umpoohg]

Thank you so much for replying...I am looking at my date table, and it does emcompass all the dates that are in my fact(header) table.  I have made sure that the date table has encompasses all the dates that are part of my header(fact) table.  It (datetable) actually has more dates than my fact table, which I thought was ok.

Prev Prd Sales = CALCULATE(SUM('IHeads'[Sales]), SAMEPERIODLASTYEAR(DateTable[Date]))

Prev prd Saless = CALCULATE(sum(IHeads[Sales]), PARALLELPERIOD('IHeads'[DateKey], -1, year))

Datekey is a datetime column

I have tried both of the methods in regards to the datefiled.  I have used the date from the datetable, as well as, using DateKey from the Fact(header) table.  I cant seem to understand why both versions are representing the same(wrong) value.  For this particular account shown below, there are $0 sales in the same period from last year (01/01/2016-02/27/2016)

However, as you see below, a value is being shown.

 

[Vvelarde]

@umpoohg

The slicers are from Date Table?

[umpoohg]

@Vvelarde Yes, the slicer is coming from the date table.  Thats why the blanks are so strange.  I even went as far to remove the "blanks" by  going to query editor and filtering the blanks or N/A.  

[kcantor]

@umpoohg

The blanks come from having dates in your fact table that are filtered out of your date dim. table. For example, my date table generally pulls back two years (on most reports not all). If my fact table has entries from 3 years back, those numbers will aggregate under blank unless I filter the dates allowed in my sales table as well as in my date table.

As far as the YTD showing multiple years, that is just part of the function. If your date table has multiple years, multiple YTDs should show unless filtered. OF course when you add Last Year to Date they should provide the totals for the pevious year.

So, if my fact table has information for 2015, 2016, and 2017 I would have YTDs for all three years but only Previous YTDs for 2016, and 2017 and I would not have any data for 2014. I would then filter my report or report page for the current year which would show only 2017 with the previous YTD being 2016.