cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Glaeran Frequent Visitor
Frequent Visitor

DAX - Calculate YTD without TOTALYTD with custom callendar.

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:

10ib8yg

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: DAX - Calculate YTD without TOTALYTD with custom callendar.

@Glaeran,

 

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

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support
Community Support

Re: DAX - Calculate YTD without TOTALYTD with custom callendar.

@Glaeran,

 

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

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors