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

JeffManley — Wed, 01 Mar 2023 15:10:11 GMT

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.

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

amitchandak — Thu, 02 Mar 2023 02:41:19 GMT

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)

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

topic DAX: new table derived from our calendar table that shows YearWeek and the BookingsBudgetWeekly in DAX Commands and Tips

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

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