Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Month and Week Budget Value

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.

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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