Problems with PYTD values

[JayZee70]

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:

PYTD Sales KG/L =

VAR _MaxDate =

CALCULATE(MAX('Total Sales'[DAY] ) -366, ALL('Total Sales'[SALES]))

Return

CALCULATE('Total Sales'[Sales KG/L], DATEADD('Calendar'[Date], -364, day), 'Calendar'[Date] <= _MaxDate)

How do I write a better code for this so I can have days where I don't sell anything of any particular product and STILL be able to show all the calculations I want?

[Cookistador · Accepted solution]

 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?

[v-achippa · Accepted solution]

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

[v-achippa]

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

[v-achippa]

Hi @JayZee70,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
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

[v-achippa]

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.
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

[Cookistador · Accepted solution]

 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?