DAX: new table derived from our calendar table that shows YearWeek and the BookingsBudgetWeekly

[JeffManley]

Goal is to create a new table derived from our calendar table that shows YearWeek and the BookingsBudgetWeeklyAllocation.

The FinanceValues table holds the BookingsBudget for each month; there are two relationships to the calendar table: YearMonth and YearWeek.

I'm easily able to figure out the weekly bookings budget per month

Weekly Bookings Budget Measure = 
    [BookingsBudgetMonthly] / 
    (CALCULATE(
        DISTINCTCOUNT('Calendar'[YearWeek])-1,
        USERELATIONSHIP(FinanceValues[YearMonth], Calendar[YearMonth])
    ))

I've tried many DAX formulas to create a new table showing the Calendar[YearWeek]and the Weekly Bookings Budget divided by the weeks per month.

Then went another route and created a calculated column [Total Weekly Allocation] in the FinanceValuestable which takes the [Value] column divied by the [WeeksInMonth] the that feeds into:

BookingsBudgetWeeklyAllocation =
CALCULATE(
[Total Weekly Allocation],
FinanceAccount[Account_level2]="Bookings",
FinanceScenario[Scenario]="Working Budget",
USERELATIONSHIP(FinanceValues[YearMonth],'Calendar'[YearMonth])
) 

and then into

BookingsBudgetWeeklyAllocationByWeekTable =
SUMMARIZE(
CALCULATETABLE(
'Calendar',
USERELATIONSHIP(Calendar[YearMonth], FinanceValues[YearMonth])
),
Calendar[YearWeek],
"BookingsBudgetByWeek", [BookingsBudgetWeeklyAllocation]
) 

Nothing seems to work. Please help with a solution.

[amitchandak]

@JeffManley , You can create date from both YYYYMM and YYYYWW and use that to join to the calendar table

Date month = date(left([YYYYMM],4) , right([YYYYMM],2) ,1)

or

Date Week=

var _st = date(left([YYYYWW],4) ,1 ,1)

var _stmon = _st  - weekday(_st , 2) -1 //monday of year start

return

_stmon  + right([YYYYWW],2)*7