Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys,
I'm beginner in DAX and Pbi.
I have a table fact with 2 dates and an amount fileds, like this :
date_insert date_order amount
21/12/2017 01/08/2017 5
13/12/2017 01/07/2017 4
01/08/2016 01/08/2015 6
01/08/2013 01/08/2010 2
There is a relation-ship with date dimension on date_insert field. I want to sum all transactions where year date order is the same that max filtered value from date dimension in a mesaure and all transactions where the year date order isn't the same that max filtered value from date dimension in another measure.
For example:
What approach can I take?
Thanks for your help
Solved! Go to Solution.
Read this over a couple times, still not certain I know what you want, but probably something like:
Measure = VAR __year = MAX('Date'[Year]) RETURN SUMX(FILTER(ALL('Table'),YEAR([date_order])=__year),[amount]) Measure 2 = VAR __year = MAX('Date'[Year]) VAR __maxAllYear = MAXX(ALL('Date'),[Year]) VAR __minAllYear = MINX(ALL('Date'),[Year]) RETURN SUMX(FILTER(ALL('Table'),YEAR([date_order])>=__minAllYear&&YEAR([date_order])<=__maxAllYear&&YEAR([date_order])<>__year),[amount])
Read this over a couple times, still not certain I know what you want, but probably something like:
Measure = VAR __year = MAX('Date'[Year]) RETURN SUMX(FILTER(ALL('Table'),YEAR([date_order])=__year),[amount]) Measure 2 = VAR __year = MAX('Date'[Year]) VAR __maxAllYear = MAXX(ALL('Date'),[Year]) VAR __minAllYear = MINX(ALL('Date'),[Year]) RETURN SUMX(FILTER(ALL('Table'),YEAR([date_order])>=__minAllYear&&YEAR([date_order])<=__maxAllYear&&YEAR([date_order])<>__year),[amount])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |