Prior YTD calculation if current year is blank

[summer18]

I have formula for YTD which is working perfectly.  Prior YTD formula only works fine if I have values for current year but if none, the result is blank.

Calculations I used:

YTD Sales =

SWITCH(
TRUE(),
[Max Year]=2019,
TOTALYTD([Actual Sales],'Metric Date'[Date])
)

Prior YTD Sales =
SWITCH(
TRUE(),
[Max Year]=2019,
CALCULATE([Actual Sales],PARALLELPERIOD('Metric Date'[Date],-12,MONTH)),
BLANK()
)

Result: 

If there are values for YTD then the output for Prior YTD is also correct but if YTD is blank, then the output becomes blank also but it should contain a value.

This should be the correct result:

[summer18]

I had a temporary solution for Prior YTD if there's no value for YTD.  I only have 2 years worth of data in my data source.  My temp solution does not work if I use the month slicer because my formula is already set to use Jan to previous month eg. May so in my Month slicer if I select Jan-April, my chart still shows data from Jan-May.  

Prior YTD Actual =
SWITCH(
TRUE(),
[Max Year]=2019,
CALCULATE([Actual Expense],PARALLELPERIOD('Metric Date'[Date],-12,MONTH)),
[Max Year]=2018,
CALCULATE([Actual Expense],DATESBETWEEN('Metric Date'[Date],DATE(2018,1,1),DATE(2018,5,1))),
BLANK()
)

Is there anyone who can help me on this?

[Anonymous]

First of all, you should check if your model has a proper DATE TABLE that covers all full years for which there is at least one date in any one of your tables. You have to specifically designate the table as DATE TABLE. Then and only then will date intelligence functions work as expected.

[summer18]

Yes my Metric Date table is marked as Date Table

[Anonymous]

Please make a screenshot of your whole model and paste.

Stachu's formulas are correct and if the model is set up correctly, they will work OK out of necessity. Looks like your model is flawed.

Best

Darek

[Stachu]

first of all you need to decide on what's the point of reference for the time in your model - I suggest using the slicer

assuming the your Actuals Sales measures looks something like this:

Actual Sales = SUM(Table[Sales])

let's consider a scenario where you selected May 2019 (not Jan-May 2019, just May 2019)

these measure should work fine for you then:

YTD Sales = CALCULATE([Actual Sales], DATESYTD('Metric Date'[Date]))
PY YTD Sales = CALCULATE([YTD Sales], SAMEPERIODLASTYEAR('Metric Date'[Date]))

with these measures the YTD will be calculated in reference to your filter context (e.g. slicer)

[summer18]

Hi Stachu,

In my case, your formula only works if there's data for current year (2019) so the PY returns correct output however, if there's no data for current year, it does not calculate correctly and returns blank.

[Stachu]

Can you share sample anonymised data from your fact and calendar tables? Also the syntax for [Actual Expense]

The Metric Date table is marked as the date table, with 1:many single direction relationship and you have month and year selected in the slicer?