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])-1
var period_selected =SELECTEDVALUE(GL_BALANCES[PERIOD_NAME])
var years2 = CALCULATE(max(GL_PERIODS[PERIOD_YEAR]),GL_BALANCES[PERIOD_NAME]=period_selected)-1
Var Result = calculate( GL_BALANCES[Total Current Asset] / GL_BALANCES[Total Current Liability],GL_BALANCES[PERIOD_YEAR]=years2)
return Result
result is showing blank in this measure.

when i use years2-1 it shows blank value. 
Thanks In Advance. 
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)

5 Replies

  • 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

      Let me clear scenrio, I am having GL balance , Period year as 2022,period_name as Jun-21-22 and I have put Period_name in slicer, when user select period I calculate period year from it as year = calculate (max(period_year),period_name=selected_period) it return me the year then what I do, I subtruct 1 from calculated year.... 
      After that I pass that year to measure for calcualtion as above. 
      the issue is when user select from slicer it narrow downs the data to that period_selection only. 
      now pls help to cater the data from previous year on selection. 

      • amitchandak's avatar
        amitchandak
        Super User
        3 years ago

        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

    • 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.