Forum Discussion

TallKewlOnez's avatar
TallKewlOnez
Helper I
6 years ago

Calculations Struggle

Ok, I hope I can explain this correctly, I have been trying to get this right for over a week.

 

I have four objects in my model,

actual transaction data (value, date, entryid) (example: 250,000, 01-01-2020, 1737)    

actual budget data (value, name, fiscal year,fiscal period, date, entryid) (example: 1,2000,000,"201901", 2019,1,2019-01-31,1737)

two dim tables

dimreportgroups (groupmajor, groupminor,entryid)  (example:  expenses,salary,1737) (joined to both fact tables)

dimentrynames (name,account,entryid) (example:  staff salary, 134, 1737)   (multiple accounts - same id) (joined to both fact tables)

 

everything about my report works, except one calculation   YtdDifference per group major 

i.e. what is the difference between the actual YTD and the budget ytd for expenses

 

I have a measure that holds the actual YTD for Expenses attached to the first fact table

I have a measure that holds the actual budget YTD for Expenses attached to the second fact table

I cannot for the life of me seem to get proper values   

 

any ideas?

 

 

Need Help

2 Replies

    • TallKewlOnez's avatar
      TallKewlOnez
      Helper I
      6 years ago

      dimReportingGroups

      EntryIDGroupMajorGroupMinor
      1737ExpensesSalaries

       

      dimEntryNames

      EntryIDNameAcct
      1737Temp Salaries221
      1737Temp Salaries222
      1737Temp Salaries223
      1737Temp Salaries224
      1737Temp Salaries225

      FctTransData

      EntryIDAcctIndexUnitValuePostDate
      17372251001/16/20 12:00 AM
      173722520001/16/20 12:00 AM
      1737225300001/16/20 12:00 AM
      173722134001/17/20 12:00 AM
      17372241200

      1/24/20 12:00 AM

       

       

      FctBudget

      BudgetNameAcctIndexValuePeriodFiscalYearEntryID
      Budget 201901221300000120191737
      Budget 201901221300000220191737
      Budget 201901221300000320191737
      Budget 201901221300000420191737
      Budget 201901222200000120191737
      Budget 201901222200000220191737
      Budget 201901222200000320191737
      Budget 201901222200000420191737
      Budget 2019012239000120191737
      Budget 2019012239000220191737

       

      So Budget for 1737 = 2018000 (for periods 1 - 4) (Yes I realise budget is 2019 bt it is being used for 2020 for now) and the budget selection is  a slicer

       

      Transdata for this set is 36700

       

      So, I need a measure I guess that would get me the variance (1981300)

       

      I have tried a custom column in each fact table that gives me the YTD value and then tried a calculate(sum, filter 

      I have tried a measure that is the same as above

      but is just keep getting the wrong number in the variance)

       

      so, supply group major which gives me entryid 1737

      which gives me the appropriate budget data when i supply budget name and period

       

      and similar for transdata (same filter of group major)   (in this example 1737 is expenses)

       

      sorry if i am not explaining well

      i need to supply the period, year and entry id for budget

      then date range for for transdata