Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BhavyaM
Helper V
Helper V

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

 

 

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

 

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.

 
 
 
 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Ashish_Mathur
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.