Forum Discussion

BhavyaM's avatar
BhavyaM
Helper V
6 years ago
Solved

Previous year same quarter values

Hi Friend, 

Please help

 

 
YearQuarterTypeSalesSales QTDSales LQTD
2018Q1A2224222242 
2018Q2B65046504 
2018Q3C1494214942 
2018Q4D2218522185 
2019Q1A76867686 
2018Q1A1626316263 
2018Q2B2619226192 
2018Q3C97469746 
2018Q4D1976919769 
2019Q1A2246822468 
2018Q1A1034510345 
2018Q2B2011720117 
2018Q3C2125621256 
2018Q4D86998699 
2019Q1A1419914199 
Total  2476134435348850  ?

Here Current Quarter is 2019 Q1 and what is the formula to get Previous year same quarter values (I.e., 2018 Q1) 

 

Sales QTD = TOTALQTD([C&L TOTAL], 'Table data'[Date])

LQTD = CALCULATE(

            SUM('Table'[Sales]),

            DATEADD('Table'[Date], -1, YEAR))

 

 

Thanks in Advance

 

 

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    BhavyaM , with date calendar 

    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • BhavyaM's avatar
      BhavyaM
      Helper V
      6 years ago

      Thanks for your help!

       

      Yes this is giving me proper results but when i search with year filter then i am not getting proper result when i calculate QOQ %

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi BhavyaM,

    It seems like you are work with text format year quarter values.
    Obviously, time intelligence function not able to work with this scenario. I'd like to suggest you extract the year and query number as a condition to calculate.

    Measure formulas:

    QTD = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Year] = MAX ( 'Table'[Year] )
            && RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
    )
)

LYQTD = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Year] = MAX ( 'Table'[Year] )-1
            && RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
    )
)

    Regards,
    Xiaoxin Sheng

    • BhavyaM's avatar
      BhavyaM
      Helper V
      6 years ago
       

      Hi Xiaoxin Sheng,

       

      Thanks for the solution.

       

      I am getting data correctly but when i filter with Year  i am not getting exact results. Please find the screenshots.

       

      Regards,

      Bhavya.

       
       
       
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI BhavyaM,

        It sounds like you directly apply the filter on your table fields, right?
        If this is a case, it will filter other year records. my formula can find out previous value but can't calculate on these ranges. (they are been filtered)

        For this scenario, I'd like to suggest you use an unconnected table(not has relationshpi to current table) as source slicer to select specific values. Then you can keep current table filters and use selected value as counting to calculated.

        QTD =
VAR selected =
    MAX ( selector[Year] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Year] = selected
                && RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
        )
    )


LYQTD = 
VAR selected =
    MAX ( selector[Year] )
RETURN
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Year] = selected - 1
            && RIGHT ( [Quarter], 1 ) <= RIGHT ( MAX ( 'Table'[Quarter] ), 1 )
    )
)

        Notice: selector is an unconnected table with year fields value extract from current table.

        Regards,

        Xiaoxin Sheng

    • BhavyaM's avatar
      BhavyaM
      Helper V
      6 years ago

      I have date column in my table 

       

      Please help me what will be my formula to get previous year same quarter values it's very urgent Requirement

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    To be able to use the Date and Time Intelligence functions, you must either have a genuine date column (which you do not have) or a Year column and a Month column.  I see you have a Year column so please please share the month column as well.