DAX syntax for sales actual vs budget

Question

Hi there,

I have 2 fact table & 2 period dimension table

  FACT_SALES_ACTUAL :
  PERIOD_DAY_KEY
  SALES_AMOUNT
 
  FACT_SALES_BUDGET :
  PERIOD_MONTH_YEAR_KEY
  SALES_BUDGET
  
  DIM_PERIOD :
  PERIOD_DAY_KEY
  DAY_DESC
  MONTH_YEAR_DESC
  PERIOD_MONTH_YEAR_KEY
  YEAR_DESC
  
  DIM_MONTH_PERIOD :
  PERIOD_MONTH_YEAR_KEY
  MONTH_YEAR_DESC
  YEAR_DESC
    
Where there are relation ship between

   FACT_SALES_ACTUAL --> DIM_PERIOD --> DIM_MONTH_PERIOD 
  
   FACT_SALES_BUDGET --> DIM_MONTH_PERIOD

questions :

how to make a DAX query of measurement of Sales Budget so it can be displayed side by side with Sales Actual measurement, in daily basis with correct relationship of month-year ?
I've tried once, bu it always show the sales Budget in every single period of day,including with unmatch period of month-year ..

please help

Thanks

Answer 1

I built a quick test model based on the info you provided and it worked exactly as you would expect.

When creating your pivots are you dragging in the month from the month table?

---

Jacob | Please mark helpful posts and answers

Answer 2

Hi Jacob,

Thanks for the reply.

No, I was not dragging in the month from the month table. The reason I create monthly period dimension is to setup as bridge table in hope that I can combine actual and budget data side by side in daily basis..

Answer 3

Is this still an issue?

Thanks!

---

Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!