Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
I have this expression:
TAM Amount = CALCULATE( SUM(CifSales[Amount]); DATESINPERIOD(CalendarTable[DateKey];LASTDATE(CalendarTable[DateKey]);-12;MONTH) )
This, calculate the TAM amount of my sales table (reference date is november 2017). The result its as follows:
With this, I need to obtain the average value of those 12 records. The correct amount is 11,834,775.87
I have tried several forms of Average and none of them works for me. ¿Can you please help me?
Thanks in advance!
Best regards,
Antonio.
Solved! Go to Solution.
AVERAGEX ( SUMMARIZE( CalendarTable;'CalendarTable'[Year]; CalendarTable[ShortMonthName] ); [TAM Amount] )
You may use a similar way as shown here.
AVERAGEX ( SUMMARIZE( CalendarTable;'CalendarTable'[Year]; CalendarTable[ShortMonthName] ); [TAM Amount] )
Hi @Achamorroman,
You need to use an AveregeX expression add another measure like this:
Average on total = IF ( HASONEVALUE ( Calendar[Years] ); [TAM Amount]; AVERAGEX ( ALL ( 'Calendar'[Year]; Calendar[ShortMonthName] ); [TAM Amount] ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix, thanks for fast reply!
Unfortunately, that formula doesn't fit what I need. This is the result:
The line result is the same, but total doesn't work.
Thanks again for your help!
Regards
A.Chamorro
Hi again,
I found this great article about Rolling 12 Months Average in DAX. I'm going to test it now, it seems to be a solution!
Regards!
A.Chamorro.
Hi @Achamorroman,
What result are you expecting?
Hi @Ashish_Mathur,
I need one graphic as follows:
From now, thanks to @mattbrice (its formula works!) I have the right total, but I don't know how can I obtain always those 12 months having a slicer as reference.
Any help would be very appreciated
Thanks!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |