Forum Discussion

pwc_ananta's avatar
pwc_ananta
Helper I
4 years ago
Solved

Last year ratio calculation

Need help in a meausre to calculate last year ratio when user slelect period from slicer.  currently using this measure as      Current Ratio LY = var years = MAX(GL_BALANCES[PERIOD_YEAR])...
Custom Visuals
Reports
  • pwc_ananta's avatar
    pwc_ananta
    3 years ago

    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)
amitchandak's avatar
amitchandak
Super User
4 years ago

,In such cases better to have a separate date/year period table with numeric year and period

 

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])))

 

 

But if you need this period vs last period

 

you need have period rank on YYYYPP

 

New column on Date/period Table

 

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

 

Measures
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))

 

pwc_ananta

pwc_ananta's avatar
pwc_ananta
Helper I
4 years ago

pls check the scnerio, only slection of period come up with blank, even its calculating year  correct , but when i subtruct 1 from year of period it contains blank in measure.