cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yfeng Frequent Visitor
Frequent Visitor

how do I sum data by week from different tables

Hi all,

 

I have a visual table that I made from different columns from different tables.

and I have a bar graph for the same set of data.

currently, the data is showing daily value. I have a time slicer that I set it week #.

the table would show daily value for the weeks I selected from the slicer.

I would like the table and the graph to show the weekly total.

How can I do that? 

 

 

6 REPLIES 6
yfeng Frequent Visitor
Frequent Visitor

Re: how do I sum data by week from different tables

I would like the BI to mimic the excel screenshot I have below.

to show the values by weeks, or by month, instead of daily.

 

5-22-2019 2-07-10 PM.jpg5-22-2019 2-11-24 PM.jpg

Community Support Team
Community Support Team

Re: how do I sum data by week from different tables

Hi @yfeng ,

 

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.

yfeng Frequent Visitor
Frequent Visitor

Re: how do I sum data by week from different tables

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.

Community Support Team
Community Support Team

Re: how do I sum data by week from different tables

Hi @yfeng ,

 

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?

 

1.png

 

 

 

 

 

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.

Community Support Team
Community Support Team

Re: how do I sum data by week from different tables

Hi  @yfeng ,
 
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards
Amy

yfeng Frequent Visitor
Frequent Visitor

Re: how do I sum data by week from different tables

Hi Amy,

 

I was thinking suming the On Dock move and the Off Dock moves together,

but they are 2 different columns in the 2 different tables. 

should I create a measure in each table first, then sum them together?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 349 members 3,474 guests
Please welcome our newest community members: