topic based on the year quarter previous year quarter value need in DAX Commands and Tips

based on the year quarter previous year quarter value need

Anonymous — Thu, 29 Oct 2020 16:58:13 GMT

Hi all,

based on the year quarter previous year quarter value need

for example if i select 2020 Q3 i need result of 2019 Q3 value

i have used the measure =

PY FY Actual rate netherland = ROUND(

CALCULATE (

SUM ( 'table'[Value] ),

FILTER (

ALL ( 'table' ),

'table'[Year]

= SELECTEDVALUE ( 'table'[Year] ) - 1

&& 'table'[business ] IN DISTINCT ( 'table'[business ] )

)

)/1000,0)

it's return correct previous year value but if i select filter it's not change it's shows only full previous year value only

how to achieve this logic?

Re: based on the year quarter previous year quarter value need

amitchandak — Thu, 29 Oct 2020 17:11:16 GMT

Anonymous , Based on what I got, please find the examples

if you have a date , then with date table and TI

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
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))))

if you do not have Date, Based on rank on year Qtr or qtr start date

new column

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

Qtr Rank = RANKX(all('Date'),'Date'[year Qtr],,ASC,Dense)

measure
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Re: based on the year quarter previous year quarter value need

gsherbon — Thu, 29 Oct 2020 19:23:27 GMT

If you have a Dates Table, then this can be done with the SAMEPERIODLASTYEAR function.

CY FY Actual rate netherland = ROUND( CALCULATE ( SUM ( 'table'[Value] ), )/1000,0)

PY FY Actual rate netherland = CALCULATE ( CY FY Actual rate netherland, SAMEPERIODLASTYEAR (DATES(Date))

Then whatever date slicer you pick will automatically calculate the same corresponding period.