Dax Sum with multiple filter

[Anonymous]

I have the following problem.

I have a table with the following columns: date, department, value1, value2, value3 (measure value1/value2)

Now I would like to have an additional value4.

This value4 should summarize the value3 for the past 30 days for every single department.

There should be the value4 on date 2019-01-31 = sum(value3) from date 2019-01-01 to 2019-01-30 for each department.

Should look like this 

date

department

value1

value2

value1/value2

sum(value1/value2) for the last 30 days

[Mariusz · Accepted solution]

Hi @Anonymous 

You can apply these measures to your dataset.

Value3 = SUMX( table, table[value1] / table[value2] )

value4 = 
CALCULATE(
    [Value3], 
    DATESINPERIOD( 'Calendar'[Date], MAX( 'Calendar'[Date] ), -30, DAY ),
    ALLEXCEPT( 'table', 'table'[Department] )
)

DATESINPERIOD is a Time intelligence function so it will require date dimension/calendar table.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz 

Thank you very much.

I forgot to mention something.

The data model contains of 5 Tables:

DIM Tables: Department, Date

Fact Tables: Fact1 with value 1, Fact2 with value 2

Measure: Measure 1 = value 1 / value 2

Both Fact Tables have relations to both dim tables

date (from Date dim)	department ( from Department dim)	value 1 (from Fact1)	value 2 (from Fact2)	value 3 (from Measure 1)	value 4 (from Measure 1)
				sum(Fact1[Value 1])/sum(Fact2[Value 2])	sum of value 3 for last 30 days for each department

[v-frfei-msft]

Hi @Anonymous ,

kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Mariusz]

Hi @Anonymous 

Can you create a data sample?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn