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.
My simple table has a Date column with date entries for each month from July 2016 through June 2017 (so twelve months total) and a Units column with the number of units corresponding to each month. I want to end up with three measures that can be used in three different card visuals in Power BI: Units for Final Month, Units for Month Before Final Month, and Units for Month Six Months Ago. I have the calculation for the first one, Units for Final Month, but cannot figure out the other two. (I would have thought I could assign a negative number counting backward from the Final Month at 0 and then filtered on the negative number associated with the desired month, but if that's possible I can't uncover it.) Here is what I have that is giving me Units for Final Month, which works great:
MonthFinal = CALCULATE(SUM('Sales'[Units]),
FILTER(ALL('Sales'[Date]) ,
'Sales'[Date] = MAX('Sales'[Date])))
What would the DAX statements be to get the Units for Month Before the Final Month, and Units for Month Occurring Six Months ago? I imagine it's straightforwarded but is eluding me. Thank you for any help you can provide!
Solved! Go to Solution.
Hi,
Create a Calendar Table with a relationship from the Date column of the Sales table to the Date column of the Calendar Table. Try these measures:
Units sold in final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-1)+1,EOMONTH(MAX(Calendar[Date]),0))))
Units sold in month before final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-2)+1,EOMONTH(MAX(Calendar[Date]),-1))))
Hi,
Create a Calendar Table with a relationship from the Date column of the Sales table to the Date column of the Calendar Table. Try these measures:
Units sold in final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-1)+1,EOMONTH(MAX(Calendar[Date]),0))))
Units sold in month before final month = CALCULATE(SUM('Sales'[Units]),DATESBETWEEN(Calendar[Date],EOMONTH(MAX(Calendar[Date]),-2)+1,EOMONTH(MAX(Calendar[Date]),-1))))
Thank you, Ashish_Mathur! That is exactly what I was looking for and I very much appreciate your help!
You are welcome.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |