Forum Discussion
PTD Last year
Hi Folks,
I do have fixed Period in the financial Calendar. I have Period start Date and Period End date in the Date Table.
To find PTD Sales I am using the following Dax:
This seems to be working fine But ia m unable to calculate PTD for last year.
I am using the following Dax but not getting expected rsults:
5 Replies
- amitchandak
Super User
Anonymous , Create a Rank on Year start date and use it with Rank. Or populate Year of strat date as year column and use that
Year Rank= RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
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))For Year Till Date, you need Day of Year
Day of Year =datediff([Year Start date] , [Date],Day) +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])))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))Refer year TI and week (Rank Approach)
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38aPower BI — 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-Last-Week/ba-p/1051123Calendar -https://youtu.be/Qt0TM-4H09U
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
'Appreciate your Kudos.Refer my update on -https://community.powerbi.com/t5/Desktop/YTD-and-YTD-Last-year-dax-when-financial-is-not-fixed/m-p/1352885#M580828
here PTD is Similar to MTD as in i have multiple start dates and end dates in a single year
- amitchandak
Anonymous , same way use
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))Day of Month =datediff([Month Start date] , [Date],Day) +1
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Day of Month] <= Max('Date'[Day of Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && 'Date'[Day of Month] <= Max('Date'[Day of Month])))In case you need a year start date using month dates
minx(Filter(Date,[Year]=earlier([Year])),[Month Start Date])
Also, see if this can help
- Greg_Deckler
Community Champion
Anonymous - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
