Forum Discussion
Last year ratio calculation
I have found a solution let me explain it to you,
When we want to bypass a slicer we will use all filter.CALCULATE([Inv.],All(GL_BALANCES),ALL(QP_REPORT_MAPPING) , GL_BALANCES[PERIOD_YEAR]=SELECTEDVALUE(GL_BALANCES[PERIOD_YEAR]),GL_BALANCES[PERIOD_NUM]=1)
pwc_ananta , You need to have separate period /date table with sortable Year, Period, and Year Period(YYYYPP) and Year Period Rank
where Period Rank column is
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))
New column if needed
Period Day =datediff([Period Start date] , [Date],Day) +1
PTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
I have found a solution let me explain it to you,
When we want to bypass a slicer we will use all filter.