YTD – “By Default, a year level was expected. No such level was found in the cube” Error in MDX

By
roopesh.valluru
-
8427
45224

Friends,

“By Default, a year level was expected. No such level was found in the cube” is the most common error in MDX when a newbie in MDX is using YTD function. YTD is one of very powerful functions provided my Microsoft in MDX. YTD gives YEAR to DATE value of the measure passed. PFB the syntax of YTD function.

YTD(Member_Exp)

Member_Exp – Valid Member Expression which returns a member from TIME Dimension Hierarchy.

For example if I pass Nov 2011 as a member to the YTD function then it calculates the sum of measure value from Jan 2011 to Nov 2011 and provide you the result. PFB a sample query which shows how to use YTD fucntion.

SUM(YTD(Member_Exp),Measure).

Let’s discuss about the most common error we get when we use YTD function i.e “By Default, a year level was expected. No such level was found in the cube”. When I started my career in SSAS and MDX I tried a lot to solve this issue and make YTD function work. I failed to and used alternate function PERIODSTODATE which helps in achieving the output required. Let’s take a sample query which has YTD function in it.

With member [Measures].[YTD] as
SUM(YTD([Dim Time].[Hierarchy].CurrentMember),[Measures].[Sales Amount])
Select {[Measures].[Sales Amount],[Measures].[YTD]} on Columns,
[Dim Time].[Hierarchy].[Calendar Quarter] on Rows
From [Adventure Works]

If you see the above query the YTD function calculates the Year to Date of Sales Amount based on Current ROW item but if you see the above screenshot it is throwing error “By Default, a year level was expected. No such level was found in the cube”. The reason for this error is the definition of your time dimension.

When ever you are using the functions like YTD,MTD,WTD and QTD then you have to make sure of the following things.

  • Dimension Type of the dimension from where you are passing member_exp to YTD function should be of TYPE TIME.
  • Calendar related Type should be set to the attributes used in the Hierarchy.

Let’s see how to change the Type of Dimension and Attributes and make this YTD function work.

  • Select the dimension and press F4 for properties window. In properties window change the Type to TIME as shown below.
  • Now select Calendar Year attribute and go to properties. Select “Years” under “Calendar Year” which is under “Date” item as show in below screenshot.
  •  Repeat the above step for the remaining attributes which are used as part of Hierarchy. Select Quarters,Months,Weeks .. from the list under Attribute type just like we selected “Years” in last step.
  • Once all the Types for attributes are selected then process the cube and run the same query which failed for YTD earlier. Yoc can see YTD getting calculated.

That’s it .. Hope you understood how to solve this issue and Happy Coding !!

Regards,

Roopesh Babu V

RELATED ARTICLESMORE FROM AUTHOR

8427 COMMENTS

  7. Свадьба –один из самых счастливых моментов нашей жизни. Так хочется чтобы теплые воспоминание об этом дне остались на вечно. Поэтому [url=https://vk.com/weddingspbvideo]свадебная видеосъемка[/url] организовывая свою свадьбу молодожены должны задуматься о качественной видео и фотосъемке, https://vk.com/weddingspbvideo чтобы спустя много лет просматривая свой свадебный фильм и фото погрузится в атмосферу этого счастливого дня. На сегодняшний день [url=https://vk.com/phvideowedding]фото и видеосъемка свадеб[/url] – это одно из составляющих этого главного дня в создании молодой семьи.

  9. Собственное производство, доставка и монтаж под ключ [url=https://karkasnik-pod-kluch.ru/]каркасный дом[/url] гарантия на готовую конструкцию, доставка 500 км за наш счет.

  18. Fantastic information, Thank you!
    [url=https://theessayswriters.com/]someone to write my essay[/url] someone to write my essay [url=https://bestcheapessaywriters.com/]do my essays[/url] i cant write my essay

  20. Terrific facts. Kudos.
    [url=https://writingpaperforme.com/]essay writter[/url] write my research papers [url=https://custompaperwritersservices.com/]paper writer services[/url] write my paper please

  28. You stated it very well!
    [url=https://ouressays.com/]thesis proposal[/url] research paper help sites [url=https://researchpaperwriterservices.com/]term paper writers[/url] writing a phd proposal

  34. Наша компания предлагает услуги по изготовлению мебели на заказ [url=http://mebel-dlya-was.ru/]мебель на заказ[/url] будет полностью соответствовать вашим потребностям и предпочтениям.

  37. Wow loads of valuable information!
    [url=https://quality-essays.com/]buy my essay[/url] buy a essay [url=https://buyanessayscheaponline.com/]buy a custom essay[/url] pay for paper

  49. You revealed this perfectly!
    [url=https://essaywritingservicelinked.com/]best resume writing service reddit[/url] college essay writing services [url=https://essaywritingservicetop.com/]top essay writing[/url] fiverr essay writing

  52. With thanks. Lots of posts!
    [url=https://homeworkcourseworkhelps.com/]do my math homework[/url] how do i concentrate on my homework [url=https://helpmedomyxyzhomework.com/]coursework writing[/url] does my child have to do homework

  55. Latvija tiessaistes kazino ir kluvusi arvien popularaki, piedavajot speletajiem iespeju baudit dazadas azartspeles no majam vai celojot. Lai darbotos legali, [url=https://steemit.com/gambling/@kasinoid/gambling-ir-spelu-veids-kas-pamatojas-uz-nejausibas-elementu-kura-speletaji-liek-naudu-uz-kada-notikuma-iznakumu-cerot-uz-uzvaru]https://steemit.com/gambling/@kasinoid/gambling-ir-spelu-veids-kas-pamatojas-uz-nejausibas-elementu-kura-speletaji-liek-naudu-uz-kada-notikuma-iznakumu-cerot-uz-uzvaru[/url] tiessaistes kazino Latvija ir jabut licencetiem no attiecigajam iestadem. Sie kazino piedava plasu spelu klastu, tostarp spelu automatus, galda speles, pokera turnirus un sporta likmju deribas.

  58. You said it nicely.!
    [url=https://essaytyperhelp.com/]help me write my college essay[/url] essay help websites [url=https://helptowriteanessay.com/]homework essay help[/url] law essay writing service

  59. На этом сайте представлена простая и удобная программа ТВ передач — [url=https://tv-programma1.ru/]программа на сегодня на[/url] на сегодня, на завтра, на неделю.

  64. Cheers! Useful stuff.
    [url=https://customthesiswritingservice.com/]write a thesis statement for your argument[/url] thesis statement for social media [url=https://writingthesistops.com/]english thesis statements[/url] thesis statement for rhetorical analysis

  67. Компания ЗубыПро представляет профессиональные услуги по надеванию, корректировке и снятию брекет-систем [url=https://intofact.ru/zdorovaya-ulybka-sovremennye-tehnologii-lecheniya-zubov/]https://zubypro.ru[/url] в Санкт-Петербурге.

  88. Простая и удобная тв программа на сегодня, [url=https://www.tv-programma2.ru/]программа на сегодня на[/url] на завтра, на неделю.

  137. 888starz is an online casino that offers players the opportunity to play various gambling games, such as slots, roulette, blackjack, and others, using cryptocurrencies https://www.facebook.com/888starz.casino
    The casino is operated by Bittech B.V. and is licensed and regulated by the Government of Curacao. In addition to traditional casino games, 888starz.bet also offers sports betting, live casino games, and virtual sports. The website has a user-friendly interface and supports multiple languages, making it accessible to players from around the world

  230. Great data. Thanks!
    [url=https://essaytyperhelp.com/]essay write help[/url] essay helper online [url=https://helptowriteanessay.com/]online essay writing help[/url] medical school personal statement

  402. Whatsminer M50 118T — это новое устройство для майнинга криптовалют [url=https://intofact.ru/whatsminer-m50-novoe-slovo-v-mire-majninga-kriptovalyuty/]https://intofact.ru/whatsminer-m50-novoe-slovo-v-mire-majninga-kriptovalyuty/[/url] обеспечивает высокую производительность и эффективность.

  529. Здесь можно [url=https://vc.ru/crypto/652334-kupit-bitkoin-v-2023-godu-kak-i-gde-bezopasno]купить биткоин[/url] без комисси и по выгодной цене!

  556. Woodworking has been a staple craft for centuries [url=https://www.motorverso.com/the-versatile-uses-of-cnc-wood-laser-cutters-in-the-automotive-industry/]https://www.motorverso.com/the-versatile-uses-of-cnc-wood-laser-cutters-in-the-automotive-industry/[/url] but the introduction of modern technology has brought about new advancements in the field.

  576. Очень удобны, быстрый и безопасный [url=https://vc.ru/u/1589010-tomas-anderson/655609-obmennik-kriptovalyut-gde-menyat-i-kakoy-vybrat]обменник криптовалют[/url]!