Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have some data that I calculate the average for but I only need to display for the current month. I have a standard date table connected to the data table. Some sample data with result look like this:
The code I am using with some comments about how I think it is working:
Avg Amnt = CALCULATE( CALCULATE( //Filter the data to just AU currency before Average Calc SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU")), //get the previous 3 months data excluding current month DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ), DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) ) //Add this for 3 mths average )/3
But I know DAX may not being working the way I think it is.
I tried to wrap another Calculate around the output and filter for just the Current Month by adding from the Date Table ,FILTER('Date', 'Date'[Month #] = 9)) but I get an odd result.
Thanks Tex
Solved! Go to Solution.
I created a couple of variables then added an IF statement to determine which variable to display.
Avg Amnt = VAR Future_Frcst = CALCULATE( CALCULATE(AVERAGEX( DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ), DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) ),CALCULATE( //Filter the data to just AU currency before Average Calc SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU")) ) ,FILTER('Date', MONTH('Date'[Date])=MONTH(TODAY())) ),DATESYTD('Date'[Date],"31/3")) Var Curr_Mth = CALCULATE( CALCULATE(CALCULATE( SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU")) ),DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) ))/3/'Date'[DaysinCurrentMonth]*'Date'[DaysLeftCurrentMonth] Return CALCULATE(if('Date'[YearMonth] < CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00")) , BLANK() , if('Date'[YearMonth] = CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00")) ,Curr_Mth ,Future_Frcst ) ))
Seems to be wokring fine
Hi Tex_01,
So your measure is like below, right?
Avg Amnt = CALCULATE ( CALCULATE ( //Filter the data to just AU currency before Average Calc SUMX ( 'Sample', 'Sample'[Amount] ), FILTER ( ALL ( 'Sample'[Currency] ), 'Sample'[Currency] = "AU" ) ), //get the previous 3 months data excluding current month DATESBETWEEN ( 'Date'[Date], DATEADD ( FIRSTDATE ( 'Date'[Date] ), -3, MONTH ), DATEADD ( LASTDATE ( 'Date'[Date] ), -1, MONTH ) ), FILTER ( 'Date', 'Date'[Month #] = 9 ) ) / 3
Could you share your raw data, not the table chart, for further analysis?
Regards,
Jimmy Tao
Thanks for looking at this Jimmy.
Date | Amount | Currency | MthSum | Prev3MthAvge | CurrentMth/Daysleft | ||
1/04/2018 | 10 | AU | 37 | 80.33333 | |||
2/04/2018 | 15 | AU | |||||
3/04/2018 | 12 | AU | |||||
1/05/2018 | 20 | AU | 79 | 131.6667 | |||
2/05/2018 | 23 | AU | |||||
3/05/2018 | 19 | AU | |||||
4/05/2018 | 17 | AU | |||||
1/06/2018 | 31 | AU | 125 | 169 | |||
2/06/2018 | 34 | AU | |||||
3/06/2018 | 32 | AU | |||||
4/06/2018 | 28 | AU | |||||
1/07/2018 | 43 | AU | 191 | ||||
2/07/2018 | 42 | AU | |||||
3/07/2018 | 46 | AU | |||||
4/07/2018 | 22 | AU | |||||
5/07/2018 | 38 | AU | |||||
1/08/2019 | 53 | AU | 191 | ||||
2/08/2018 | 58 | AU | |||||
3/08/2018 | 33 | AU | |||||
4/08/2018 | 47 | AU | |||||
1/09/2018 | 76 | AU | |||||
2/09/2018 | 65 | AU | |||||
3/09/2018 | 33 | AU | 56.33 | ||||
1/04/2018 | 12 | US | |||||
2/04/2018 | 17 | US | |||||
3/04/2018 | 14 | US | |||||
1/05/2018 | 22 | US | |||||
2/05/2018 | 25 | US | |||||
3/05/2018 | 21 | US | |||||
4/05/2018 | 19 | US | |||||
1/06/2018 | 33 | US | |||||
2/06/2018 | 36 | US | |||||
3/06/2018 | 34 | US | |||||
4/06/2018 | 30 | US | |||||
1/07/2018 | 45 | US | |||||
2/07/2018 | 44 | US | |||||
3/07/2018 | 48 | US | |||||
4/07/2018 | 24 | US | |||||
5/07/2018 | 40 | US | |||||
1/08/2019 | 55 | US | |||||
2/08/2018 | 60 | US | |||||
3/08/2018 | 35 | US | |||||
4/08/2018 | 49 | US | |||||
1/09/2018 | 78 | US | |||||
2/09/2018 | 67 | US | |||||
3/09/2018 | 35 | US |
Hopefully this helps.
I created a couple of variables then added an IF statement to determine which variable to display.
Avg Amnt = VAR Future_Frcst = CALCULATE( CALCULATE(AVERAGEX( DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ), DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) ),CALCULATE( //Filter the data to just AU currency before Average Calc SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU")) ) ,FILTER('Date', MONTH('Date'[Date])=MONTH(TODAY())) ),DATESYTD('Date'[Date],"31/3")) Var Curr_Mth = CALCULATE( CALCULATE(CALCULATE( SUMX('Sample','Sample'[Amount]),FILTER(ALL('Sample'[Currency]), 'Sample'[Currency]="AU")) ),DATESBETWEEN('Date'[Date], DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) ))/3/'Date'[DaysinCurrentMonth]*'Date'[DaysLeftCurrentMonth] Return CALCULATE(if('Date'[YearMonth] < CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00")) , BLANK() , if('Date'[YearMonth] = CONCATENATE(YEAR(TODAY()), FORMAT(MONTH(TODAY()), "00")) ,Curr_Mth ,Future_Frcst ) ))
Seems to be wokring fine
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |