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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
1001
Resolver II
Resolver II

Help to Solve a Date DAX.

Hi All, I have a Date table issue I would like to resolve in my DAX. 

I have a w Week filter, that when selecting a week number, would like to sum the Month to Date data. However, if I select Week 14 from my filter list per the table below, I only get data for Week Ending 1/10/2023. But, I need it to sum Weeks 11 to 14 coresponding to Month Name September. My current DAX is below. Much appreciated in advance.    

 

Current MTD = CALCULATE(([HD Volume]),DATESMTD(Dates[Week Ending]))

 

 

DateTable.png 

3 REPLIES 3
sjoerdvn
Super User
Super User

your date table should have a date column, and your measure should then be simply:

Current MTD = CALCULATE([HD Volume],DATESMTD(Dates[Date]))
123abc
Community Champion
Community Champion

It seems like you want to calculate the Month-to-Date (MTD) value based on the selected week number in your filter. If I understand correctly, you want to sum the data for the weeks corresponding to the selected month.

To achieve this, you need to adjust your DAX measure to filter the data based on the selected week number and sum the values for the weeks within the corresponding month. Here's a modified version of your DAX measure:

 

Current MTD =
CALCULATE(
[HD Volume],
FILTER(
ALL(Dates),
Dates[Week Number] <= MAX(Dates[Week Number]) &&
Dates[Month Name] = VALUES(Dates[Month Name])
)
)

 

This measure calculates the MTD value by summing the [HD Volume] for the weeks that are less than or equal to the maximum week number selected in the filter and belong to the same month as the selected week.

Please replace [HD Volume] with your actual column or measure name, and make sure to adjust the table and column names based on your data model.

This DAX measure should provide you with the desired Month-to-Date calculation based on the selected week number.

 
 
 
 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Thanks 123abc, giving that a try now. Kind thanks.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.