Forum Discussion

JayZee70's avatar
JayZee70
Helper I
1 year ago
Solved

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 anyt...
Tips and Tricks
  • Cookistador's avatar
    Cookistador
    1 year ago


     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's avatar
    v-achippa
    1 year ago

    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's avatar
v-achippa
Community Support
1 year ago

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's avatar
v-achippa
Community Support
1 year ago

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's avatar
    v-achippa
    Community Support
    1 year ago

    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's avatar
      v-achippa
      Community Support
      1 year ago

      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