Lookup and sum with criteria

[thebeginner1204]

I am an absolute DAX beginner and am looking for a solution to the following problem:

In the following table there are companies, departments, date (monthly period) and a value.

Now I am looking for a way to create a DAX Querry which sums up all companies for a specific department (100 in the example).
This in the current year and in the last year. A date table is already created.

Questions:

Which is the best variant for this case?
Calculate(sum(Lookupvlaue... or Calculte(sum(filter....

Are there any examples for this case?

Thanks for help

[Jihwan_Kim]

Hi,

I am not sure about how your expected outcome looks like, but in case if you want to create a table visualization, please check the attached file and the below picture.

[thebeginner1204]

@Jihwan_Kim thanks for the answer. the table would clearly be the easiest. but I need the values in a measure for other visuals and further calculations.

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please check the below picture and the attached pbix file.

I added a measure.

[thebeginner1204]

Thank you very much for the file and the answer.
The aim is to have all values in 2 periods in different measures.

Measure 1: Sum of all companies in division 100 from January 2021 (last year/month dynamically using Date column).

Measure 2: Sum of all companies in derivation 100 (January 2022) from current year (last year/month dynamically using Date column)

The correct data includes further departments (e.g. 200 etc.).

The calculations in this case are liquid assets (from a balance sheet).
Finally, I need the total per department in a period (independent of the company), in individual measures which I can then use further (e.g. balance sheet ratios, liquidity level etc).

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Could you please share your sample pbix file's link? I think only seeing the excel file's screenshot makes me a little bit confuse to create a solution.

[thebeginner1204]

@Jihwan_Kim 

clear here in the link for download: https://xfl.jp/MexV9p 
The structure of the data does not differ much from the demo data in Excel.

Here is another explanation:
These are raw data from a financial programme. For this, departments (cost centres of departments) would have to be processed across several companies.

To do this, I have to sum up individual cost centres (departments) across companies (Company column).

Department (cost centre) 100 = Cash and cash equivalents
Department (cost centre) 130 = Inventories

In the end, the totals are then put in relation (%) to each other.
For example: 100 (Sum uf all Deparemnets) / 130 (Sum uf all Deparemnets).

The difficulty, however, lies in the period (history). In order to display this on a monthly basis (e.g. on a line chart), I have to create measures in advance where I can then calculate the ratios. The ratios are then combined (e.g. with a target value) in the line chart.

In the meantime I have tried and tried with "lookupvalue" and there it seems to be limited because I need several criteria (this year -1 and department 100 for example).

thx for help