IF Clause on different level of granularity

[joshua1990]

I have a budget table that shows me the budget for each department:

Year-Week	Department	Budget
2021-01	Mounting	500
2021-02	Mounting	400

And then I have a Sales table that shows me the sales per Article

Article	Year-Week	Sales
A	2021-01	500
B	2021-01	400

Using an Attribute Table I have also the relation between the article and Department. Both tables are also linked by a calendar table.

What I would like to determine now is the Budget per Department, but if there is no Budget the actual Sales should be represented.

For Instance for department 'ABC' there is no Budget, but Sales. Here the new measure should show the sales. 

In the end I have a matrix that shows me the department on row level and year-Week per column.

How is this possible?

IF(ISBLANK(Budget[Budget]), Sales, Budget

This will probably not work, right?

I assume I need a SUMX

[VahidDM]

Hi @joshua1990 

You tables are connected with the calendar table, so there is no connection between Department and Article. My question is Do you want to show the same date sale amount as a department budget? IF yes, try this:

Measure =
Var _A = max(Budget[Year-Week])
Var _Sale = calculate(sum(Sales[Sales]),filter(all(Sales),Sales[[Year-Week]=_A))

IF(ISBLANK(Budget[Budget]),_Sale,Budget[Budget])

Can you share a tables with those connection columns (if that is a Date column)

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/