Forum Discussion
Problems with PYTD values
Hello
I am having some issues with my code for PYTD sales.
The one that I am using is working fine BUT there is an issue when some sales are NOT coming in every day.
Then my tables won't show anything cause I get an error message telling me that is something wrong with my PYTD code.
The message is pointing to the "Dateadd" sequence. And I get that if it is a "hole" in the sales pattern this will happen.
This is the code:
Why are you not using SAMEPERIODLASTYEAR ?And your measure becomes something like:
PYTD Sales KG/L =
CALCULATE(
'Total Sales'[Sales KG/L],
SAMEPERIODLASTYEAR('Calendar'[Date])
)If it is not what you are trying to achieve, can you share a small sample of your data and the expected result?
Hi JayZee70,
Thank you for reaching out to Microsoft Fabric Community.
The issue occurs because DATEADD requires continuous date values, and missing sales days can cause the calculation to break.
Use the below measure so that all days are included even when sales data is missing:
PYTD Sales KG/L = VAR _MaxDate = MAX('Calendar'[Date]) --> To get the latest available date
RETURN CALCULATE(SUM('Total Sales'[Sales KG/L]),
DATESBETWEEN('Calendar'[Date],
_MaxDate - 364, -- Start date: 364 days ago
_MaxDate -- End date: Latest available date
)
)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
5 Replies
Why are you not using SAMEPERIODLASTYEAR ?And your measure becomes something like:
PYTD Sales KG/L =
CALCULATE(
'Total Sales'[Sales KG/L],
SAMEPERIODLASTYEAR('Calendar'[Date])
)If it is not what you are trying to achieve, can you share a small sample of your data and the expected result?
Hi JayZee70,
Thank you for reaching out to Microsoft Fabric Community.
The issue occurs because DATEADD requires continuous date values, and missing sales days can cause the calculation to break.
Use the below measure so that all days are included even when sales data is missing:
PYTD Sales KG/L = VAR _MaxDate = MAX('Calendar'[Date]) --> To get the latest available date
RETURN CALCULATE(SUM('Total Sales'[Sales KG/L]),
DATESBETWEEN('Calendar'[Date],
_MaxDate - 364, -- Start date: 364 days ago
_MaxDate -- End date: Latest available date
)
)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi JayZee70,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa