Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
sujitjena
Resolver I
Resolver I

How to create custom YTD function

Hi Everyone,

I tried to create time intelligent function - TOTALYTD but it doesnt work for Fiscal year sorting based on my data set. So i want to create a custom YTD function that can add the values of the months cumulatively based on custom sorting (in my case Sep to Aug). Is this possible?

1 ACCEPTED SOLUTION

@sujitjena,

 

Please note that all your LTD and LYTD calculations are working correctly. But when trying to display Calendar year and ordered by financial month, it gives the wrong message. Please check my pbix file shared

LYTD GP Total = TOTALYTD(sum('GP Deployed'[Total]),DATEADD(Dates[Date],-12,MONTH),"8/31") 

 

Screenshot 2019-12-26 16.38.31.png

 

https://www.dropbox.com/s/9g9uuqdcbvwexxz/PBIX%20View.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

14 REPLIES 14
chawalit
Helper I
Helper I

You must input the 3rd parameter in TOTALYTD function as the Year-End-Date. For example, if your fiscal year start 01/07/2019 (dd/mm/yyyy) the TOTALYTD will like this TOTALYTD( [Total Sales], 'Date'[Date], "06-30" ).

 

Hope this help.

Hi @chawalit : Yes, the 3rd parameter works. However, my problem is in the data set and not the TOTALYTD function. The actual data set is little wiered. since my Financial year starts from Sep, the date is tagged from Sep 16 to Aug 17 as 2017. Hence, the need to create a custom YTD function. 

Hi @sujitjena  if you mean your fiscal year 2017 start form 
Sep 16, Oct 16,  Nov 16, Dec 16, Jan 17, Feb 17, Mar 17, Apr17, May 17, Jun17, Jul17, Aug 17

So, like our fiscal year start from Oct 16 - Sep 17 as fiscal year 2017. My dax for YTD as below:

SalesYTD_FC = TOTALYTD( [Total Sales], 'Date'[Date], "09-30" )

Screen Shot 2562-12-26 at 15.06.00.png



 

@chawalit : I understand the TOTAL YTD function but as i said my data is a bit weird. Please check the sample data set in the below link and let me know if we can have a custom function for YTD and LYYTD

 

https://www.dropbox.com/sh/kvofpp1iijhy6wa/AAB3JM4s-NHy9GTeQQfoQD3wa?dl=0

 

Thanks for your help!

@sujitjena  please check your fiscal year, fiscal month I think is incorrect. Like a fiscal year 2017 you said it will start from Sep 16 - Aug 17. But, in your dataset it start Sep 17, Oct 17, Nov 17, Dec 17, Jan17, Feb 17 - Aug 17. I think after you clear your fiscal year your dax will correct.

Screen Shot 2562-12-26 at 21.28.16.png

@chawalit : Yes you are right - My data set is weird. So, i was wondering if could change the YTD formula instead of correcting the data set. Anyways thanks for your help!

@sujitjena Sure, and you should to correct you dax for GP YTD and GP LYYTD like this:

GP YTD = TOTALYTD( [GP Deployed total], Dates[Date], "08-31" )
GP LYYTD = CALCULATE( [GP Deployed total], SAMEPERIODLASTYEAR( DATESYTD( Dates[Date], "08-31" ) ) )
the output will look like this:
Screen Shot 2562-12-27 at 15.30.57.png

 

amitchandak
Super User
Super User

Is it different from https://community.powerbi.com/t5/Desktop/TOTALYTD-Not-Working-for-Fiscal-Month/m-p/884102#M423692

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak : Its related to the same data in my earlier post and thanks for your help on that. Since my data set is not as per the actual year (2017 Sep is actually 2016 Sep till 2017 Dec is actually Dec 2016). So the time intelligence doesnt produce the right results even though it works great. So, i was wondering if i can create my own custom YTD function.

Try this

 

Cumm YTD = CALCULATE(sum('GP Deployed'[Total]),DATESBETWEEN(Dates[Date],STARTOFYEAR(Dates[Date],"8/31"),ENDOFMONTH(Dates[Date])))

 

 

I think all other calc is also correct

But remember the sort put sep before Jan, in the case calendar year. So in Cal year 2017. We are seeing sep to dec of another year. Say 2018. And As data start from Jan it shows Jan 2017 to Aug 2017  as one year. 

 

In case of 2018, the cal does not reset on Jan 2018, because it gets data from dec

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak : You are right and i do understand the system configuration for time intelligence functions. So, as i see it now my only option is to change the data source to reflect the actual year ( Sep - Dec 2017 will change to Sep - Dec 2016 in the period column of fact table "GP Deployed").

 

Let me know if we can create custom function for YTD and LYYTD for any sort in Months of a year.

Thanks Again!

@sujitjena,

 

Please note that all your LTD and LYTD calculations are working correctly. But when trying to display Calendar year and ordered by financial month, it gives the wrong message. Please check my pbix file shared

LYTD GP Total = TOTALYTD(sum('GP Deployed'[Total]),DATEADD(Dates[Date],-12,MONTH),"8/31") 

 

Screenshot 2019-12-26 16.38.31.png

 

https://www.dropbox.com/s/9g9uuqdcbvwexxz/PBIX%20View.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
v-shex-msft
Community Support
Community Support

Hi @sujitjena ,

Can you please share some sample data for test? It is hard to coding formula without any same data.

How to Get Your Question Answered Quickly 

BTW, you can create a sorting table to achieve custom sorting, but it not works for time intelligence functions.

Custom Sorting in Power BI  
Please use date function to manually filter on table records for rolling calculations. 

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft : Sure, Please find the sample PBIX file at below link.

 

https://www.dropbox.com/sh/kvofpp1iijhy6wa/AAB3JM4s-NHy9GTeQQfoQD3wa?dl=0

 

Thanks,

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.