How to measure a SUM of any value for each week for the current month filtered

Question

I have a visual that I need to calculate the total volumes of sales, I have linked this table of volumes with another table that contains month and week numbers.

I'm having trouble writing a measure that calculates the volume of sales in the first week of the month, then another measure for the second week, the third, and onwards.

I want to filter the month of reference, and show me the total of sales for the respective first week, second week, third, etc...

I tried to use the measure weeknumber = MIN(), weeknumber = MIN()+1, weeknumber= MIN()+2 ,but it seems the measure don't accept for me to add number with the MIN formula,

Anyone has an idea of how can I solve this?

Thanks!

Answer 1

Step1. add [Month] [Week] column into sales table, calculate [sales volume] measure for next step:

I have linked this table of volumes with another table that contains month and week numbers

based on your description, i think can use RELATED formula but should make sure you have created one-multiple relationship between two tables and the direction should be from another table to sales table:

columns:

Month=related('another table',[Month])
Week=related('another table',[Week])

measure:

Sales volume = sum('Table'[Sales])

Step2. create a new table to summarize sales volume of each week:

New table = 
summarize('Table','Table'[Month],'Table'[Week],
"sales volume",[Sales volume])

Step3. calculate week numbers per month with add new column [week no. per month] into New table:

Week NO. per Month = 
rankx(filter('New table',[Month]=earlier('New table'[Month])),
[Week],[Week],asc)

Step4. create any visuals you want: