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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors