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.
Hello,
I have a calculated column:
SalesMTD = VAR RowDate = DimDate[Date] RETURN CALCULATE ( SUM ( FactInternetSales[SalesAmount]), FILTER All(DimDate[Date]), DimDate[Date] <= RowDate && MONTH ( DimDate[Date] ) = MONTH ( RowDate ) ) )
DimDate is my date table, and FactInternetSales[SalesAmount] comes from the AdventureWorks sample. I have MTD data for three years, and I want to get the average of three months each year; that is, I want July 2012, July 2013, and July 2014 MTD's averaged together. This is driving me nuts. Does anyone know how to do this?
Thanks,
Howard
Hi,
Share some data and also show the expected result.
Hello,
Here is some data:
Date MTD Expected calculation ========================================== July 1, 2011 5 July 2, 2011 10 July 1, 2012 5 July 2, 2012 10 July 1, 2013 5 5 July 2, 2013 10 10
The expected computation is ([July 1, 2011] + [July 1, 2012] + [July 1, 2013])/3.
Thanks,
Howard
Hi,
Share the link from where i can download your PBI file.
Hi,
This measure should work
Average on same day in previous 3 years = AVERAGEX(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",SUM(FactInternetSales[SalesAmount]),"EFGH",CALCULATE(SUM(FactInternetSales[SalesAmount]),DATESBETWEEN(DimDate[Date],EDATE(MIN(DimDate[Date]),-1),EDATE(MIN(DimDate[Date]),-1))),"IJKL",CALCULATE(SUM(FactInternetSales[SalesAmount]),DATESBETWEEN(DimDate[Date],EDATE(MIN(DimDate[Date]),-2),EDATE(MIN(DimDate[Date]),-2)))),[ABCD]+[EFGH]+[IJKL])
When i drag this measure to the visual, i receive a message saying that not enough memory is available. Try this on a smaller dataset.
Thanks, Ashish. I really appreciate your help.
Howard
Hi,
Did my solution work?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |