YTD calculations for november to october calendar year and not regular jan-dec year

anee123 — Fri, 11 Dec 2020 06:19:41 GMT

Hi All,

I need to calculate the distinct count of oppurtunity id year wise and display the numbers till date. Say for example today is 11-dec-2020. I need to display the distinct count of activations for 2020 and 2019. I am able to achieve this if the year starts from jan and ends at dec.

The complexity increases when the calendar year is supposed to start from nov 1 and end at oct 31. In this case, i need to show the values from nov 1 2020 till date (dec 11 2020) and compare it with previous year same date range i.e nov 1 2019 to dec 11 2019 and

i am not getting the desired result in dax, when i use visual level filter i am able to see the distinct count i.e count value of 1 in (nov 1 2019 till dec 11 2019) but how to achieve this in dax, can anyone help me here.

I am attching the pbix file and the source file in the below google drive link :

https://drive.google.com/file/d/1I65JyZZAlJpo_wWJ3A_Jv37kJnRtnZiv/view?usp=sharing

https://drive.google.com/file/d/1qUlCG_nkipQ6unWj4TbLGUXKuPyjLbgw/view?usp=sharing

Thanks,

Aneesh

Re: YTD calculations for november to october calendar year and not regular jan-dec year

CNENFRNL — Fri, 11 Dec 2020 08:52:38 GMT

anee123 , the best practice to make full use of time intelligence functions is to leverage a standard calendar table in the data model. If the date column isn't a standard one, unexpected results pop up. An important reason is that almost all time intelligence functions conduct context transition implicitly during the evulation; such behaviors, no doubt, add up difficulties.

topic YTD calculations for november to october calendar year and not regular jan-dec year in DAX Commands and Tips

YTD calculations for november to october calendar year and not regular jan-dec year

Re: YTD calculations for november to october calendar year and not regular jan-dec year