Forum Discussion

Priya2007's avatar
Priya2007
Frequent Visitor
5 years ago
Solved

YTD issue

  Hi,  I am trying to get YTD for my application. In my table ,I don't  have date field only fiscal period( starts from july - June) For example I need to select only 202102 to get the below sum....
  • amitchandak's avatar
    amitchandak
    5 years ago

    Assuming period 1 means jun. Create a fiscal period table with these new columns (I called it as date)

     

    year = left(fiscal_period,4)
    period = right(fiscal_period,2)

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

     

    Then Try these measure
    YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[period] <= Max('Date'[period]) ))
    LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[period] <= Max('Date'[period])))



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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Assuming period 1 means jun. Create a fiscal period table with these new columns (I called it as date)

 

year = left(fiscal_period,4)
period = right(fiscal_period,2)

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

 

Then Try these measure
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[period] <= Max('Date'[period]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[period] <= Max('Date'[period])))



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