DAX - Calculate YTD without TOTALYTD with custom callendar.

[Glaeran]

Hey,

Small note here is that below situation takes place in SSAS Tabular Model, but as I read and understood, PowerBI model is based on SSAS Tabular model therefore I'd allow myself to ask it here as I was unable to find answers/help on MSDN.

We're deploying tabular project for one of our customers and are trying to calculate YTD measrues.

Calculation looks as follows:

Measure YTD := TOTALYTD([Measure];'TIME'[Fiscal Year Week End Date];"6/30")

Measure works fine as long as [Fiscal Year Week End Date] column is pulled from TIME table into Excel Pivot.

However if used with other ex. [Fiscal Year Week Start Date] -> [Measure YTD] column is returning same values as [Measure].

That's how date data looks like on tables:

The join in model is done between [date_id] columns between two tables since as you can see certain [date_sk] can exist twice (ex. 20161001) in two different quarters and providing different data on measures.

Data that we're operating on, comes with weekly granularity from source system and we're unable to change it. So implementing a continuous date range data table wouldn't change anything in our situation.

Additionally, the only unique identifier for us right now to use is [date_id] column which is varchar type containing week date and quarter ex. '20161001OND'. However, as mentioned earlier, certain week can be placed in two different quarters, therefore, there's also '20161001JAS' and that's how we get our data.

My question here is - how to properly create a measure in SSAS Tabular Model using DAX that would calculate YTD value of certain Measure assuming that our FY End Date is 6/30.

I'm happy to provide more details if needed to solve the case.

[v-chuncz-msft · Accepted solution]

@Glaeran,

For any time intelligence function, you could implement a custom DAX formula.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/