Post reply

DAX Query - QoQ Growth, reset at beginning of each Year

  • July 21, 2017 at 10:50 am

    #149369

    I have created a simple Model, 1 Fact table, 1 Date table and 1 Dimension Table.
    The data relates to captured metric of Actual against Targets.  Metric is 1 year long and broken down by Quarter.  
    I can match Actual Against Target, but what I'm failing with is tracking Quarter on Quarter growth, where at the beginning of the new year it resets.  I can get QoQ or YoY through all years.

    Is what I'm looking for possible?  If so can someone help

  • July 23, 2017 at 1:31 am

    #1952257

    darrenkelly - Friday, July 21, 2017 10:50 AM

    I have created a simple Model, 1 Fact table, 1 Date table and 1 Dimension Table.
    The data relates to captured metric of Actual against Targets.  Metric is 1 year long and broken down by Quarter.  
    I can match Actual Against Target, but what I'm failing with is tracking Quarter on Quarter growth, where at the beginning of the new year it resets.  I can get QoQ or YoY through all years.

    Is what I'm looking for possible?  If so can someone help

    This shouldn't be a problem, have you looked through the 35 Time Intelligence Functions in DAX?
    😎

    1. CLOSINGBALANCEMONTH Function (DAX)
    2. CLOSINGBALANCEQUARTER Function (DAX)
    3. CLOSINGBALANCEYEAR Function (DAX)
    4. DATEADD Function (DAX)
    5. DATESBETWEEN Function (DAX)
    6. DATESINPERIOD Function (DAX)
    7. DATESMTD Function (DAX)
    8. DATESQTD Function (DAX)
    9. DATESYTD Function (DAX)
    10. ENDOFMONTH Function (DAX)
    11. ENDOFQUARTER Function (DAX)
    12. ENDOFYEAR Function (DAX)
    13. FIRSTDATE Function (DAX)
    14. FIRSTNONBLANK Function (DAX)
    15. LASTDATE Function (DAX)
    16. LASTNONBLANK Function (DAX)
    17. NEXTDAY Function (DAX)
    18. NEXTMONTH Function (DAX)
    19. NEXTQUARTER Function (DAX)
    20. NEXTYEAR Function (DAX)
    21. OPENINGBALANCEMONTH Function (DAX)
    22. OPENINGBALANCEQUARTER Function (DAX)
    23. OPENINGBALANCEYEAR Function (DAX)
    24. PARALLELPERIOD Function (DAX)
    25. PREVIOUSDAY Function (DAX)
    26. PREVIOUSMONTH Function (DAX)
    27. PREVIOUSQUARTER Function (DAX)
    28. PREVIOUSYEAR Function (DAX)
    29. SAMEPERIODLASTYEAR Function (DAX)
    30. STARTOFMONTH Function (DAX)
    31. STARTOFQUARTER Function (DAX)
    32. STARTOFYEAR Function (DAX)
    33. TOTALMTD Function (DAX)
    34. TOTALQTD Function (DAX)
    35. TOTALYTD Function (DAX)

  • July 23, 2017 at 8:50 pm

    #1952291

    Also take a look at this
    http://www.daxpatterns.com
    It may even have the calc you are after.

  • July 23, 2017 at 10:08 pm

    #1952295

    There's even an example XLS file...
    http://www.daxpatterns.com/budget-patterns/

    If you want Q1 to ignore the previous quarter (Q4 of previous year), you could do something simple like

    IF(Calendar[Quarter]="Q1",BLANK(), SUM([BudgetQty]))

  • August 3, 2017 at 3:11 am

    #1953905

    Apologies for the delay in responding - just completed two upgrades, SharePoint 2007 to SharePoint Online and SQL 2008 to SQL 2016, now that the dust has settled, I have some spare capacity again 🙂

    Thanking you all for providing me with the information and links.

    Much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply