Previous year same quarter values

[BhavyaM]

Hi Friend, 

Please help

Year	Quarter	Type	Sales	Sales QTD	Sales LQTD
2018	Q1	A	22242	22242
2018	Q2	B	6504	6504
2018	Q3	C	14942	14942
2018	Q4	D	22185	22185
2019	Q1	A	7686	7686
2018	Q1	A	16263	16263
2018	Q2	B	26192	26192
2018	Q3	C	9746	9746
2018	Q4	D	19769	19769
2019	Q1	A	22468	22468
2018	Q1	A	10345	10345
2018	Q2	B	20117	20117
2018	Q3	C	21256	21256
2018	Q4	D	8699	8699
2019	Q1	A	14199	14199
Total			247613	44353	48850  ?

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

[v-shex-msft · Accepted solution]

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

[amitchandak · Accepted solution]

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


Appreciate your Kudos.

[BhavyaM]

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 %

[v-shex-msft · Accepted solution]

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]

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.

[v-shex-msft]

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

[Ashish_Mathur]

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. 

[Greg_Deckler]

Typically you need a separate Date table when using "time intelligence" functions. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[BhavyaM]

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