Forum Discussion

Nurry90's avatar
Nurry90
Icon for Helper I rankHelper I
5 years ago

Last YTD calculation non standard date table

Hi All,

 

I am trying to do two things in one;

 

1 - Create a calculation for last year to date total ( I have used the below calculation, but it is not giving me totals)

2 - Within my calculation, I would like it to be used within different contexts/filters. I.E. I want to be able to use the same calculation in a table with Month in Year (e.g May - FY2020) and within a table with week and year (e.g wk 52 - FY2020).

 

I am using a non-standard date table so cannot use the built in YTD functions.

 

LYTD =
VAR Currentmonth = SELECTEDVALUE('Date Table'[Fiscal Month])
VAR Currentweek = SELECTEDVALUE('Date Table'[FY Week number])
VAR CurrentYear = SELECTEDVALUE('Date Table'[Fin Yr])

RETURN
CALCULATE (
[Current Sales],
FILTER(ALL('Date Table'),
'Date Table'[FY Week number] = Currentweek &&
'Date Table'[Fiscal Month] = Currentmonth &&
'Date Table'[Fin Yr] = CurrentYear - 1))
 
Any help would be appreciated.

5 Replies

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

    Nurry90 , In case you have year start date you can get this easily using a calendar table

    New columns

    Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense) //option , you can use year of year start date

    Year Week = [Year]*100 +[Week]

    Day of Year =datediff([Year Start date] , [Date],Day) +1

     

     

    Try measures like 

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])


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

     

    YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
    LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

     

    YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
    LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))


    YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
    LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))

     

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

     

    1.Creating Financial Calendar - From Any Month
    https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
    2.Any Weekday Week - Start From Any day of Week
    https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Your filters would only calculate the sales for the same monthweek* of last year, but not the Last Year To Date.

     

    * - that monthweek is a mythical being, you cannot guarantee that the same month last year had the same week numbers

     

    If you want to calculate an actual LastYTD then what you need is to add a calculated column to your dates table that flags all days smaller than "TODAY() minus one year" as valid (true), and then add that filter to the SAMEPERIODLASTYEAR() call.

    • Nurry90's avatar
      Nurry90
      Icon for Helper I rankHelper I
      5 years ago

      Hi lbendlin ,

       

      I would not have the calculation on the same table, I just want to use the same calculation in both scenarios. Is it possible to do this?