Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Forum - I have a measure below that looks at the Order Date and the Due Date (both on my Orders table). Due Date is connected to my date table.
The formula says return the sum of the quantity ordered when the order date month is 2 months (or more) earlier than the due date month. The only problem is that it does not factor in the year. So, in the table below, the Sept 2019 order is not being picked up because the formula believes that 9 is greater than 5...which it is...but in this case I also need the Year to be taken into consideration. So, September (month 9) 2019 is of course less than May (month 5) of 2021.
Not sure how to modify the measure below to account for the year as well when the order date month is larger than the due month (but the order date year is in the previous year). Any suggestions?
Solved! Go to Solution.
@Anonymous , Try like
VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date] <= eomonth(Orders[Due Date],-3)+1 ) // or use -2
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
or
VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
var _date = date(year(Orders[Due Date]), month(Orders[Due Date])-2, day(Orders[Due Date]))
VAR nTable = FILTER(vTable,Orders[Order Date] <= _date ) // or use -2
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
Many Many thanks @amitchandak
Iused this version and it worked fine. Also, I had figured out another way that I think works, but yours is more elegant. My version is posted below yours.
@Anonymous , Try like
VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date] <= eomonth(Orders[Due Date],-3)+1 ) // or use -2
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
or
VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
var _date = date(year(Orders[Due Date]), month(Orders[Due Date])-2, day(Orders[Due Date]))
VAR nTable = FILTER(vTable,Orders[Order Date] <= _date ) // or use -2
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
Many Many thanks @amitchandak
Iused this version and it worked fine. Also, I had figured out another way that I think works, but yours is more elegant. My version is posted below yours.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
62 | |
61 | |
52 | |
39 | |
23 |
User | Count |
---|---|
85 | |
58 | |
45 | |
43 | |
38 |