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

By
roopesh.valluru
-
42
67324

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

42 COMMENTS

  4. Наслаждайтесь азартом и захватывающими ставками на 1win, ведущей платформе для онлайн-игр и ставок. Здесь вы найдете широкий выбор спортивных событий, казино-игр и живых дилеров, доступных 24/7.

  15. 戰神賽特
    「戰神賽特」— 2025年最火爆的老虎機，等你來挑戰！
    如果你正在尋找一款高賠率、刺激又充滿神秘感的老虎機遊戲，那麼「戰神賽特」絕對是你的最佳選擇！這款由RG電子精心打造的遊戲，以古埃及戰神賽特為主題，結合精美畫面與震撼音效，帶你進入神秘的沙漠世界，感受無與倫比的刺激體驗。
    為何玩家瘋狂愛上戰神賽特？
    超狂賠率 51,000 倍：一轉瞬間，財富翻倍不是夢
    高RTP達96.89%：更高勝率，更多機會贏得大獎
    獨特遊戲機制：掉落消除+獎金購買，玩法多樣更刺激
    極致視聽享受：古埃及風格設計+震撼音效，讓你完全沉浸其中
    無論你是老虎機高手還是新手玩家，「戰神賽特」都能帶給你前所未有的遊戲體驗！快來挑戰戰神，贏取屬於你的豐厚獎勵！

  19. USDT娛樂城
    Maispin——2025年最新USDT娛樂城，安全、快速、刺激！
    歡迎來到 Maispin，2025年最具潛力的新秀 USDT娛樂城！在這裡，您只需提供 錢包地址 即可註冊，無需繁瑣的個人資訊，享受 安全、匿名、快速 的遊戲體驗。Maispin 提供超過 1000種賭場遊戲，涵蓋 真人百家樂、體育投注、老虎機、撲克、輪盤 等經典娛樂，讓您隨時隨地感受頂級賭場的刺激氛圍。我們與世界知名遊戲供應商合作，確保遊戲 公平公正，並提供高額彩金、獎勵活動，讓玩家輕鬆贏大獎！作為 USDT區塊鏈娛樂城，Maispin 存提款秒到帳，無需繁瑣審核，資金流動安全透明，讓玩家更安心地享受遊戲樂趣。立即加入 M宇宙，體驗前所未有的加密娛樂新潮流！

  21. Flaunch is the leading blockchain gaming launchpad, designed to help game developers and investors thrive in the Web3 gaming ecosystem. By offering secure token launches, NFT integrations, and decentralized crowdfunding, Flaunch enables game creators to fund, develop, and scale their projects with full transparency and community-driven support. Whether you’re a developer or an investor, Flaunch provides the tools to connect and grow in the blockchain gaming space. https://flaunch.tech

  26. Vibracion de motor
    ¡Vendemos sistemas de balanceo!
    Somos fabricantes, manufacturando en tres ubicaciones diferentes: España, Argentina y Portugal.

    Nuestros dispositivos son de muy alta calidad, y como no actuamos como intermediarios, sino productores directos, nuestro tarifa es más competitivo que el de nuestros competidores.

    Realizamos envíos a cualquier parte del planeta, lea la descripción de nuestros equipos de equilibrio en nuestro sitio web.

    El dispositivo de balanceo es fácil de transportar, de poco peso, lo que permite ajustar el equilibrio de todo tipo de rotores en distintos ambiente.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

seventy seven + = eighty six