Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to build a DAX measure to calculate previous 3 month average of a measure. right now i have a data that start from Jan 2020 to Aug 2020. Currently i have this two DAX code but it gives me different result.
2. prev 3 Months average =
Solved! Go to Solution.
Hi @Greg_Deckler @amitchandak , Thank you for your time.
I already resolved the issue. I need only to sort the date in Visualization.
@JWick1969 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Hi Greg,
Good day!
I'm using the below code in my actiual PBI to get the average of previous 3 months and i'm getting incorrect values. May i know what is the problem with my code. Thank you.
Result:
@JWick1969 , Try like Assumes [rec %] is a measure
Rolling 3 till last 1 month = CALCULATE([rec %],DATESINPERIOD('DateTable'[Date],ENDOFMONTH(dateadd(DateTable[Date1,month)),-3,MONTH))
In case you want to use AVERAGEX('Table','Table'[rec %]) the use like
AVERAGEX(values('DateTable'[Month-year]),[rec %])
Hi @amitchandak , Thank you for the reply. Tried the code and the result is replicated the value of rec% and not getting the average of previous 3 months. below is the DAX Measure
@JWick1969 , Month and year in the visual are coming from the date table and the date table is marked as a date table, right click on the table there is an option
also try
previous 3 months = CALCULATE([rec %], DATESINPERIOD(DateTable[Date], LASTDATE(DATEADD(DateTable[Date], -1,MONTH)), -3,MONTH), all(Date))
Hi @amitchandak , Already mark DateTable as Date Table and still i'm getting different result. Please see below actual result using two different DAX Code.
@JWick1969 - Can you post sample source data in your table as text that replicates this issue? Way easier to test and get to a resolution. You may also find these helpful, you can just create the filters yourself using like EOMONTH, etc.
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi @Greg_Deckler @amitchandak , Thank you for your time.
I already resolved the issue. I need only to sort the date in Visualization.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |