Forum Discussion
Dax Sum with multiple filter
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 |
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
4 Replies
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.
LinkedInThank 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 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.
