Forum Discussion
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.
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)))
1 Reply
- amitchandak
Super User
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)))