Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Glaeran
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
v-chuncz-msft
Community Support
Community Support

@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
v-chuncz-msft
Community Support
Community Support

@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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors