Forum Discussion
Previous year same quarter values
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 ShengBhavyaM , 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 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