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
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]))
your date table should have a date column, and your measure should then be simply:
Current MTD = CALCULATE([HD Volume],DATESMTD(Dates[Date]))
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
16 | |
14 | |
14 | |
9 |