Forum Discussion
Previous year same quarter values
- Anonymous6 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 - 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.
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.