Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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?
@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-Calend...
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-Powe...
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.
Hi @amitchandak ,
I do not have a set start date for fiscal year. The data that I am working off is weekly data that comes in. Week 1 is not the same start date each year so i am not sure these would work.
@Nurry90 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
If you have Year (FY) and Week
Then year week Rank should also work
Year Week =[Year]*100 + [Week]
Year Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //option , you can use year of year start date
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
24 | |
22 |