TOTALYTD Not Working for Fiscal Month

[sujitjena]

Hi Everyone,

I have created a Custom Date table for Fisal period Month end August with below DAX:

Dates = VAR BaseCalendar =CALENDARAUTO(8) RETURN GENERATE(BaseCalendar, VAR BaseDate = [Date] VAR YearDate=YEAR(BaseDate) VAR MonthNumber = MONTH(BaseDate) RETURN ROW("Day",BaseDate, "Year", YearDate, "Month Number",MonthNumber, "Month", FORMAT(BaseDate,"MMMM"),"Year Month", FORMAT(BaseDate,"MMM YY")))

Now, This Date table is connected to my Fact table (GP Deployed) with One - Many relationship and my calendar months are sorted by fiscal months i.e September to August. However, When i apply time inteligence function TOTALYTD with 2 scenarios of DAX 

Scenario 1: GP Deployed FYTD = TOTALYTD(SUM('GP Deployed'[Total GP Deployed]),Dates[Date]),

FYTD result of TOYTALYTD Scenario 1

Scenario 2: GP Deployed FYTD = TOTALYTD(SUM('GP Deployed'[Total GP Deployed]),Dates[Date],"8/31"),

FYTD result of TOTALYTD scenario 2

it still calculates YTD from January to December eventhough the order of the months are sorted as per fiscal period. This seems weired. Any suggestions will be helpful

[Ashish_Mathur]

Hi,

Does this measure work?

=CALCULATE(SUM('GP Deployed'[Total GP Deployed]),DATESYTD(Dates[Date],"31/8"))

Select a year in the slicer.

[sujitjena]

Hi @Ashish_Mathur - Thanks for your reply.

No - the measure doesnt work. I also tried with year slicer but no luck. I have uploaded the PBIX file at below link for your reference

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

Thanks for your help!

[amitchandak]

Please check the following.

1. Do you see all the dates in Jan month in your calendar?

2. Is there any other filter on your visual

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.

Thanks.

My Recent Blog -

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

[sujitjena]

Hi @amitchandak - Thanks for your response.

1. Do you see all the dates in Jan month in your calendar? - Yes i see all dates in all of the months

2. Is there any other filter on your visual - There are no other filters.

I have uploaded the PBIX file to dropbox at below link

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

Thanks for your help!

[amitchandak · Accepted solution]

This is because in month year display you are using the calendar year. You need to use the Financial year.

Please refer

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

You seem to have some data in Sep 2021, not sure why(dates are there)

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

[sujitjena]

Hi @amitchandak - Thank you for your quick response. I understand the problem now. When i see the PBIX file you shared - The data is now showing only from Jan to Aug in case of 2017 which i understand and also the data for 2020 has shifted to 2021. So do i need to change the year in data source or this can still be done in DAX.

Again, Thanks a lot. this was very helpful. Best Regards.

[mwegener]

Hi @sujitjena ,

did you filter the year?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[sujitjena]

Hi @mwegener  - Thanks for your response. It doesnt work with or without the filters.

I have uploaded the PBIX file at below link for your reference

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

Thanks for your help!