Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi everyone, I have a dataset of date, country, volume and a start date. What I want to achieve is to get a sum of last 3months volume base on start date.
I created this measure when I put it in my visual is showing blank. My visual - bar chart require the Country as the X axis. and Y axis as the value.
Measure:
Last 3mths Vol = CALCULATE(SUM('Table'[Volume]),DATEADD('Table'[Start Date],-3,MONTH))
by right for country A it will be the sum of Dec 2020 - Feb 2021.
Month Country Volume Start Date
01/01/21 | A | 5 | 01/03/21 |
01/02/21 | A | 10 | 01/03/21 |
01/03/21 | A | 50 | 01/03/21 |
01/10/20 | A | 5 | 01/03/21 |
01/11/20 | A | 6 | 01/03/21 |
01/01/21 | B | 7 | 01/01/21 |
01/02/21 | B | 8 | 01/01/21 |
01/10/20 | B | 7 | 01/01/21 |
01/12/20 | B | 2 | 01/01/21 |
Solved! Go to Solution.
@yybi123 , Try this, this should use start date country wise, of the country is in context
Rolling 3 = CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX(Table[Start Date]),-3,MONTH))
@yybi123 , with help from a date table
Rolling 3 = CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3= CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
I need the last 3 months sum base on the start date. Each country might have a different start date.
For example , A will start on Mar 2021 and B start on Jan 2021.
So the last 3mths sum from start date for A will be sum of Dec 2020-Feb 2021 and B will be Oct 2020-Dec 2020.
@yybi123 , Try this, this should use start date country wise, of the country is in context
Rolling 3 = CALCULATE(SUM('Table'[Volume]),DATESINPERIOD('Date'[Date ],MAX(Table[Start Date]),-3,MONTH))
I tried this
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |