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.
I have a table like below and created the Measures like MTD_Current_Volume and MTD_Previous_Volume .Now i need individual Average of current and previous month of volume in diffrent columns like avg Current month and avg previous month.
MTD_Current_Volume = TOTALMTD([Volume_Total],DATESMTD(WHIN_Sales[DimDate.Date]))
MTD_Previous_Volume = CALCULATE([Volume_Total], PREVIOUSMONTH(DATESMTD(WHIN_Sales[DimDate.Date])))
Above formulas are which i have used in the DAX. i'm not able to get the average of both the measure. appriciate if anyone get the answer.
Solved! Go to Solution.
Hi @harib,
It seems the first one doesn't return the right value. Please check out.
You'd like the results of the previous month, but you don't have a date context. What should the previous month be?
Please refer to the demo in the snapshot. If I don't make any selection, the previous is out of range of the date table.
Please try it and verify the results.
Best Regards,
Dale
Hi @harib,
My pleasure.
Let's take the below scenario as an example. There are only two records in October. The AVERAGE function will return (100 + 210) / 2 = 155 while (100 + 210) / 31 = 10. Which one do you prefer? Can you hardcode the 31? If you have the answers to these two questions, the solution could be clear.
2018-10-01 100
2018-10-02 210
Best Regards,
Dale
I'm guessing you need the average per plant, if that's the case something like this should work:
Avg Current Month = VAR __Plants = ADDCOLUMNS ( SUMMARIZE ( Table, Table[DimPlant.PlantName] ), "Val", [MTD_Current_Volume] ) RETURN AVERAGEX ( __Plants, [Val] )
you just need to adjust the table name
Hi Stachu, i need output like below table. I got average but used below dax function .
MTD_Current_Volume_Avg = [MTD_Current_Volume]/21.
MTD_Previous_Volume_Avg = [MTD_Previous_Volume]/31.
but this is not the correct way and the one which you have given also not working. Can u check below image for refrence.
Thanks
Hari
Hi,
Share the link from where i can download your PBI file.
Hi Hari,
What's the formula of [Volume_Total]? Please try this formula. If it doesn't work, please provide a sample of your original data. Please mask the sensitive parts first.
MTD_Current_Volume_Avg = AVERAGEX ( 'table', [Volume_Total] )
MTD_Previous_Volume_Avg = CALCULATE ( [MTD_Current_Volume_Avg], PREVIOUSMONTH ( whin_sales[dimdate.date] ) )
I would suggest you use an independent Date table.
Best Regards,
Dale
Thanks for your reply . 1st formula is worked fine but the second is not working. Attached screen shot.
Hi @harib,
It seems the first one doesn't return the right value. Please check out.
You'd like the results of the previous month, but you don't have a date context. What should the previous month be?
Please refer to the demo in the snapshot. If I don't make any selection, the previous is out of range of the date table.
Please try it and verify the results.
Best Regards,
Dale
HI Dale,
Thank you very much for your solution, but results are coming diffrent when compare to below two measure. I have attached screen shot. Please clear this. Thanks in Advance
MTD_Previous_Volume_Avg :
Hi @harib,
My pleasure.
Let's take the below scenario as an example. There are only two records in October. The AVERAGE function will return (100 + 210) / 2 = 155 while (100 + 210) / 31 = 10. Which one do you prefer? Can you hardcode the 31? If you have the answers to these two questions, the solution could be clear.
2018-10-01 100
2018-10-02 210
Best Regards,
Dale
you could just do
Avg = ([MTD_Current_Volume]+[MTD_Previous_Volume])/2
i need individual Average of current and previous month of volume in diffrent columns like avg Current month and avg previous month.
Thanks for your quick responce.appriciate if you can come up with solution of above msg
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |