Month and Week Budget Value

[RichelleZach]

Hi,

I am trying to figure out the Month and Week Budget vs actual.  I am using chiclet slicer with month and week filter.

Budget Dates =
VAR BaseCalendar =
CALENDARAUTO (1)
RETURN
GENERATE (
BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR ( BaseDate )
VAR MonthNumber = MONTH ( BaseDate )
RETURN ROW (
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Month", FORMAT (BaseDate,"mmmm"),
"Year Month", FORMAT (BaseDate, "mmm yy")
))

and used this formula for week count

Week = CONCATENATE("Week ",RIGHT(CONCATENATE("0",WEEKNUM('Budget Dates'[Date])),2))

The month is properly showing the budget amount and actual. However, when I select week no. the budget amount is empty or the table is not showing anything.

I would like to select a week then the budget month value will not move or it will stay as month budget while the actual sales are based on week actual sales.

Thank you in advance.

[v-xicai]

Hi @RichelleZach ,

You may create relationship between your fact table and the calendar table on date field, and change the Cross filter direction from Single to Both, which will take the two tables treated as a single table.

If you make some selections in your slicer visuals, and the other visuals that contains related fact table fields return blank, that means there is no matched values with the filtered values.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RichelleZach]

Hi @v-xicai  Amy,

Thank you for your reply. I tried that it did not work.

I want to achieve the following using the following data.

Actual Sales is based on daily basis

Budget Data is based in per month

Then in the visual if I selected Week, I want the Budget Amount to stay in Monthly Value.

Thanks,

RichelleZach