Contiguous Date Selections, Invalid Numeric Representations, and General Confusion

[djkoenig]

Hello Experts, 

I'm new to Power BI and running into an issue with dynamically calculating MTD, QTD, YTD. My values are correct in summation, however, when I add a slicer to the report, my measures using SAMEPERIODLASTYEAR error out. I receive an error stating that my measure is not using a contiguous date selection.

Formula: YTD Y1 Dynamic = Calculate(Sum(YY1_BI_Customer_Sales[NetAmount]), SAMEPERIODLASTYEAR(YY1_BI_Customer_Sales[ActualGoodsMovementDate - Copy]), YY1_BI_Customer_Sales[Offset Year]=-1)

This makes sense. Actual Goods Movement Date is not a complete set of all dates.

So, I figured I could hook my actual goods movement date to a calendar table. I built the relationship and it is 1:N as expected. 

However, when I change the syntax to evaluate the Calendar Table, as opposed to the Actual Goods Movement Date (Sold Date) in the Sales table, I receive a different error message: "Invalid Numeric Representation of a Date Value has been encountered". 

Formula 2: Invalid Test = Calculate(Sum(YY1_BI_Customer_Sales[NetAmount]), SAMEPERIODLASTYEAR('Calendar'[Date]), 'Calendar'[Offset Year]=-1)

This was not producing a value. However, writing this post has solved that portion of the issue. I realized the problem was because I auto-generated a calendar table via CALENDARAUTO. Just say NO folks.  

I now receive a value for my measure, but it's the wrong value. It seems to only be taking into account the offset year and summing the total of the entire offset year, instead of the YTD portion. I'd imagine this is b/c of my last clause and the fact that I'm not very good at DAX, but I find it confusing that the same general syntax did produce a correct value in Formula 1. Also, QTD and MTD work, just changing offset year to offset quarter and month respectively in Formula 1. 

A picture so you know I'm not making this up: 

Standard - YTD, QTD, and MTD are all the correct fractional amounts of their counterparts Prior Year Total, PQ Total, etc. 

With Product Filter/Slicer Applied

"Fixed" Using Formula 2 - However, 'Invalid Test' should not equal 'Prior Year Total'. 

So, after all this, I think my question is surrounding the formula SAMEPERIODLASTYEAR. How does it know what period I am referencing/how do I define this when utilizing a calendar table? 

Thanks for reading the novel and hopefully thanks for an answer! 

[djkoenig]

Hello @JohnnySaade, 

Interestingly enough removal of the offset year then gives me the entirety of all Sales, even with the SAMEPERIODLASTYEAR clause. 

The basic formula 

Invalid Test = Calculate(Sum(YY1_BI_Customer_Sales[NetAmount]), SAMEPERIODLASTYEAR('Calendar'[Date])) seems to only be paying attention to the first arguement. I feel this would suggest an improper relationship between the calendar table and the sales table. But, I can place the info in a Maxtrix/Table and it displays properly with the correct allocation of Sales per product. 

[JohnnySaade]

Can you share an image of the relationship and any other relevant information maybe a small saple of the table YYY1?

[JohnnySaade]

What is the purpose of 'Calendar'[Offset Year]=-1? Is it possible to remove that argument? Please use SAMEPERIODLASTYEAR will already dynamically offset the period you have selected to last year's.