Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

measure not counting values from prior year

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?

 

Start of Month minus 2 =

VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date Month] <= Orders[Due Date Month] -2)
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
 
order date month.jpg
 
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Anonymous
Not applicable

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.  

 

Start of Month minus 2 v.2 =
VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date] <= eomonth(Orders[Due Date],-2))
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
 
My version
Start of Month minus 2 =

VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date Month] <= Orders[Due Date Month] -2 || Orders[Order Date Month] <= Orders[Due Date Month] +10 && Orders[Order Date Year] < Orders[Due Date Year])
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
 
 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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.  

 

Start of Month minus 2 v.2 =
VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date] <= eomonth(Orders[Due Date],-2))
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
 
My version
Start of Month minus 2 =

VAR vTable = CALCULATETABLE(Orders,ALL('Date Table'[Date]))
VAR nTable = FILTER(vTable,Orders[Order Date Month] <= Orders[Due Date Month] -2 || Orders[Order Date Month] <= Orders[Due Date Month] +10 && Orders[Order Date Year] < Orders[Due Date Year])
VAR _Result = SUMX(nTable,[SumX of Qty Ordered])
RETURN
_Result
 
 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.