Forum Discussion
Calculations Struggle
Please supply data.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
dimReportingGroups
| EntryID | GroupMajor | GroupMinor |
| 1737 | Expenses | Salaries |
dimEntryNames
| EntryID | Name | Acct |
| 1737 | Temp Salaries | 221 |
| 1737 | Temp Salaries | 222 |
| 1737 | Temp Salaries | 223 |
| 1737 | Temp Salaries | 224 |
| 1737 | Temp Salaries | 225 |
FctTransData
| EntryID | AcctIndex | UnitValue | PostDate |
| 1737 | 225 | 100 | 1/16/20 12:00 AM |
| 1737 | 225 | 2000 | 1/16/20 12:00 AM |
| 1737 | 225 | 30000 | 1/16/20 12:00 AM |
| 1737 | 221 | 3400 | 1/17/20 12:00 AM |
| 1737 | 224 | 1200 | 1/24/20 12:00 AM
|
FctBudget
| BudgetName | AcctIndex | Value | Period | FiscalYear | EntryID |
| Budget 201901 | 221 | 300000 | 1 | 2019 | 1737 |
| Budget 201901 | 221 | 300000 | 2 | 2019 | 1737 |
| Budget 201901 | 221 | 300000 | 3 | 2019 | 1737 |
| Budget 201901 | 221 | 300000 | 4 | 2019 | 1737 |
| Budget 201901 | 222 | 200000 | 1 | 2019 | 1737 |
| Budget 201901 | 222 | 200000 | 2 | 2019 | 1737 |
| Budget 201901 | 222 | 200000 | 3 | 2019 | 1737 |
| Budget 201901 | 222 | 200000 | 4 | 2019 | 1737 |
| Budget 201901 | 223 | 9000 | 1 | 2019 | 1737 |
| Budget 201901 | 223 | 9000 | 2 | 2019 | 1737 |
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