Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

PYTD / YOY QTD formula issue

I have data that is only giving a quarterly date so the typical PYTD calculation is not working due to the time intelligence. I am trying to use this formula below but am getting an error saying "STARTOFQUARTER" is not of type DATE" What can I do from here? Or can anyone point out an issue with this formula or provide a better functioning formula?

YoY PQTD =
    // Current Quarter To Date
    VAR CurrentQTD =
        CALCULATE (
            SUM (OWSSummaryByQuarterByEU[channel_licenses_net_added] ),
            // Filter for the current quarter
            FILTER (
                OWSSummaryByQuarterByEu,
                 OWSSummaryByQuarterByEu[quarter].[Date]>= STARTOFQUARTER('EU Report'[Today])
                && OWSSummaryByQuarterByEu[quarter].[Date] <= TODAY()
            )
        )
    // Previous Year's Same Quarter To Date
    VAR PreviousYearQTD =
        CALCULATE (
            SUM (OWSSummaryByQuarterByEU[channel_licenses_net_added]),
            // Filter for the same quarter of the previous year
            FILTER (
                OWSSummaryByQuarterByEu,
                OWSSummaryByQuarterByEu[quarter].[Date] >= STARTOFQUARTER ( DATEADD ( 'EU Report'[Today], -1, YEAR ) )
                && OWSSummaryByQuarterByEu[quarter].[Date] <= DATEADD ( 'EU Report'[Today], -1, YEAR )
            )
        )
    // Return the YoY difference
    RETURN
        ( CurrentQTD - PreviousYearQTD ) / ABS ( PreviousYearQTD )




 

"DAX " "DAX help" 'Need help'
"PYTD"
Column Formula Assistance
DAX Formulas
QTD
  • lbendlin's avatar
    lbendlin
    1 year ago
    I have data that is only giving a quarterly date so the typical PYTD calculation is not working due to the time intelligence.

    Add a derived date column to your fact table (for example the first day of the quarter).  Link that to your calendar dimension.  Use the time intelligence functions.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago
    I have data that is only giving a quarterly date so the typical PYTD calculation is not working due to the time intelligence.

    Add a derived date column to your fact table (for example the first day of the quarter).  Link that to your calendar dimension.  Use the time intelligence functions.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,
    I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,
    I just wanted to kindly follow up to see if you had a chance to review the previous response provided by community members. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.
    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,
    We haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
    If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.
    If you have any further questions, feel free to reach out.
    Thank you for being a part of the Microsoft Fabric Community Forum!