YTD and YTD Last year dax when financial is not fixed.

[Anonymous]

Hi All,

I want to calculate YTD and YTD last year when my financial is not fixed :

                 Year                      Start Date                End Date

For eg :     Fy19                  03.09.2018                  01.09.2019

                 Fy20                  02.09.2019                  30.08.2020

                 Fy21                  31.08.2020                  29.08.2021

The built in functions do not work here. Any help is appreciated.

Thanks in Advance!

@amitchandak @Greg_Deckler 

[Anonymous]

@Anonymous 

You need to transform your data table first, then related the FY tables with Date.

You should have a calendar table.

Table = CALENDAR(DATE(2019,1,1),DATE(2021,12,31))

Then, you should have a table for each FY including a date column and sales column. For example:

FY 2019 Table

Date	Sales
2019.1.1	20
2019.1.31	33
2019.2.3	20
...

Regards
Paul

[amitchandak]

@Anonymous , Create a date calendar

calendar(date(2018,09,03), Date(2021,08,29)) //Seem like a  364 days year, can be set with start date

Now update those year  =

start Date = maxx(filter(FYDate[Date]<=FY[End Date] && Date[Date]>=FY[Start Date]),[start Date])

// Same way Copy the dates

of Year Seq =Quotient(datediff(min(Date[Date]), Date[Date],day),364)  //This will create year as 0, 1,2,4,

FY = Year(Min(Date[Date])) +Year Seq

Now you can start date end etc

Create a Year Rank start Date or use year its self for Time Intelligence

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))

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 YTD

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])))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Power 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/1051123

Create Date calendar -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.

[Greg_Deckler]

@Anonymous - Do you have some sample transactional data that you can post as text in a table? Yes, time "intelligence" is next to useless when you have a custom financial calendar.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008