Forum Discussion
how do I sum data by week from different tables
Community SupportHi Anonymous ,
You can create column named Week to calculate the week number in table KPIDates.
Week = WEEKNUM('DateKey'[Date])
Then, create measure Sum Per Week like DAX below..
Sum Per Week = CALCULATE(SUM('DataTable'[Your sum field]),FILTER(ALLSELECTED('DataTable'),'DataTable'[Week]=MAX('DataTable'[Week])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
I tried the WEEKNUM function, but it was telling me it is not allowed as part of calculated column DAX expressions on DirectQuery models.
is there any workaround while keeping it as Direct Query?
Also, the "E on dock" and "W on dock"
"DF MMR" and "RF MMR" are measures I created, both from different tables.
- v-xicai
Hi Anonymous ,
Based on my test connect to SQL Server Data base using Directquery mode, the WEEK function worked fine, could you show me the error message?
For the measures, you can use SUMX(table,[measure]) function.
Sum Per Week = CALCULATE(SUMX('sum field Table', [Your sum field]),FILTER(ALLSELECTED('DataTable'),'DataTable'[Week]=MAX('DataTable'[Week])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.