Hi Team,
I am not able to allocated the weekly budget. There are 4 tables, Budget, Date, Sales and Company.
Company Table has got 2 records
Code
UK
US
On page If I filter by company, then it should only show filtered company budget per week.
Currently I am calculating
Total Budgets = CALCULATE(SUM(‘Budget’[Value]),
TREATAS(VALUES(Dates[MonthYear]),‘Budget’[MonthYear]),FILTERS(Company[Company]))
I am calculating
BudgetPerDay =
VAR BudgetPerMonth = CALCULATE([Total Budgets],FILTER(Dates,MONTH(Dates[Date])=MONTH(MAX(Dates[Date]))),FILTERS(Company[Company]))
RETURN
IF
(ISFILTERED(Dates[Date]),
DIVIDE(BudgetPerMonth,[WorkingDays in Month],0),[Total Budgets])
BudgetPerWeek =
IF
(ISFILTERED(Dates[Date]),
([BudgetPerDay]*[WorkingDays in week]),[Total Budgets])
Budget table structure and value example.
| Code | Description | Value | NoOfWorkingDays | PerDayValue | MonthYear | Company | Date | Year |
|---|---|---|---|---|---|---|---|---|
| A | A | 36250 | 21 | 1726 | 8 2021 | US01 | 01 August 2021 | 2021 |
| A | A | 33750 | 19 | 1776 | 5 2021 | US01 | 01 May 2021 | 2021 |
| A | A | 33750 | 20 | 1688 | 4 2021 | US01 | 01 April 2021 | 2021 |
| A | A | 33750 | 23 | 1467 | 3 2021 | US01 | 01 March 2021 | 2021 |
| A | A | 33750 | 20 | 1688 | 2 2021 | US01 | 01 February 2021 | 2021 |
| A | A | 32500 | 20 | 1625 | 1 2021 | US01 | 01 January 2021 | 2021 |
| B | B | 31250 | 21 | 1488 | 12 2021 | US01 | 01 December 2021 | 2021 |
| B | B | 42500 | 21 | 2024 | 10 2021 | US01 | 01 October 2021 | 2021 |
| B | B | 36250 | 21 | 1726 | 8 2021 | US01 | 01 August 2021 | 2021 |
| B | B | 33750 | 19 | 1776 | 5 2021 | US01 | 01 May 2021 | 2021 |
| B | B | 33750 | 20 | 1688 | 4 2021 | US01 | 01 April 2021 | 2021 |
| B | B | 33750 | 23 | 1467 | 3 2021 | US01 | 01 March 2021 | 2021 |
| B | B | 33750 | 20 | 1688 | 2 2021 | US01 | 01 February 2021 | 2021 |
| B | B | 32500 | 20 | 1625 | 1 2021 | US01 | 01 January 2021 | 2021 |
When I give Week start date or end date (x axis) then the graph then it shows correct weekly budget and filtering correctly with company.
But when I give weeknumber in the graph(x axis) then it doesn’t show the correct values.
On page there is no year filter. I have made 2021 per week measure buts it giving error.
BudgetPerWeek 2021 =
IF
(ISFILTERED(Dates[Week Number]),(IF(FILTER(Dates,Dates[Year]=2021),[BudgetPerDay])))
Can you please suggest what to be done.
Thanks.
