Buget vs Actual on one single table

[Anonymous]

I have the following transactions table:

CCost	Account	Date	Budget	Actual	Description
1	1	20190101	100	10	Gas
1	1	20190102	100	50	Salary
1	1	20190201	200	50	Gas
1	1	20190202	200	150	Salary
1	1	20190203	200	10	Gas

CCenter is the Cost Center, Account is the bank account, date is the date of the transaction, budget is the budget for that Account, for that Cost Cost, for that specific year-month. The Actual column is the value of the transaction and Description is the description of the transcation. So for example, in January 2019 the company spent 60 dollars on salary and gas. And the budget was 100 - not 200. And in February 2019, 210 dollars was spent and the budget was 200. 

I want to create a matrix, tha display the right budget values, but it sums all the values - January 200, and Februaray 600 dollars.

How can I do that, using Dax or Power Query  and preferably , not creating a new table. 

[avknishokpbi]

Try this dax measure

totalbudget =
Var budgetbymaxdate = CALCULATE(sum(Table1[Budget]),FILTER(Table1,Table1[Date]=ENDOFMONTH(Table1[Date])))
Return
budgetbymaxdate

[Anonymous]

@avknishokpbi,

It worked, but sorry, I thought it was not relevant, but I am using a date table to filter the sales table.

CalendarTable                                       Sales

Date      <----------------------------->  Date

Month                                                    CCenter

Day                                                         Account

Year                                                        Budget

                                                               Actual

                                                               Description

So using the measure that you said does not works when I use slicers with CalendarTable's columns.

[avknishokpbi]

@Anonymous

Try this DAX

TotalbudgetbyMonth =
var maxvalue = SUMX(SUMMARIZE(DateTable,DateTable[Month],"amt",MIN(Table1[Budget])),[amt])
return
maxvalue

[Anonymous]

@avknishokpbi, almost working.

When I use a drill down, it shows the smallest value intead of the sum.

[Interkoubess]

Hi @Anonymous,

Did you try in the case of the budget the average function, because it seems the budget amount is repeated...

Let us know

Ninter

[Anonymous]

Hi @Interkoubess, using the average only works if the the matrix is on the last drill level. Look the example:

The right budget is 300 not 160. How can I do that?

[v-danhe-msft]

Hi @Anonymous,

Based on my test, I could not understand why the average value in '2019' shoud be 300? It seems that the total value in 2019 should be 800 and the average should be 800/5(date) and equal to 160. Could you please offer me more information about what you want to calculate?

Regards,

Daniel He 

[Anonymous]

Hi @v-danhe-msft, 

I understood why the average is 160, but I do not want it. Looking on the sample dataset, the values of the budget get repeated for every sale. For example:

Dataset:

One sale on the day:

1

1

20190101

100

10

Gas

Two sales on the day:

1	1	20190101	100	10	Gas
1	1	20190101	100	50	Gas

Four sales on the day:

1	1	20190101	100	10	Gas
1	1	20190101	100	50	Gas
1	1	20190201	100	50	Gas
1	1	20190201	100	150	Gas

For the Cost Center 1 - Account 1 - 2019 January, independent  of the number of the sales, the budget for Gas is 100 dollars. So I want to display 100 dollars on matrix using drill down.

[Interkoubess]

Hi @Anonymous,

How  the budget is populating?

Could you please check this column formula based on the first date ( I don't know how the budget is populating),

Budget_modified = IF(DAY('Table 0'[Date])=1,'Table 0'[Budget],0)

In summary I make the budget equals to zero but the first day of the month.

Let me know if it does not work.....

Ninter

[Anonymous]

@Interkoubess, most of time there are more than one sale per day.