Forum Discussion

id013's avatar
id013
Icon for Helper V rankHelper V
6 years ago

SamePeriodLastYear with Fiscal Calendar

Hi,

So I know a similar question has been posted before but I feel like my situation is a little differnt than previously posted questions. So our fiscal year starts in April 1 and ends March 31. So subsequently our Fiscal Years are combination values so instead of 2018, 2019 etc we have Fiscal Year 2018/2019, Fiscal Year 2019/2020. 

So I'm able to create a calculation that uses that value by using a combination of various string functions:

CountforPreviousYear =
VAR CurrentYear = ALLSELECTED(dim_date[fiscal_year])
VAR PY1 = LEFT(CurrentYear,4)-1
VAR PY2 = RIGHT(CurrentYear,4)-1
VAR PrevYear = PY1&"/"&PY2
RETURN
CALCULATE([cnt_value],FILTER(ALL(dim_date), dim_date[fiscal_year] = PrevYear))
 
So this gives me total value count for the whole of last year but my visualization actually breaks up the current year into fiscal periods (P01, P02 etc) so ideally I'd like to be able to see how each period in the current year compares to the last, which this function doesn't do. Also my visualization rolls up to fiscal quarter so I'd realistically like to be able to view the count for the fiscal quarter from last year as well. 
 
Thanks for any help you can offer 
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Use datesytd of totalytd. Both allow year end

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"3/31"))

    s

    • id013's avatar
      id013
      Icon for Helper V rankHelper V
      6 years ago

      Thanks for the quick suggestions so far 😄 

      Just to clarify I wanted something that would work in the line graph visual, here is a current one:

      So when I mouse over the period the tooltip shows me the current sales value for the year, what I would like is a way to also show last years value for the same period? Is this even possible with the fiscal calendar we are using? 

      • id013's avatar
        id013
        Icon for Helper V rankHelper V
        6 years ago

        I probably should also mention that the fiscal periods themselves are not consistent either. We have 13 fiscal periods, so P01 if this year may be Apr 1 to May 3, but last year P01 was Apr 1 to May 2. ...Fun Right?

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi id013 ,

     

    You could Refer to this document to calculate the data you want and put it into "toolips"

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.