Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Urgent: Help with creating a better date function, that round up and down to full months.

Hey community. Please help fix my date formula below.   I have a question for the formula below that i have used to see financial data comparisons going back to "previous period", which basically m...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

Anonymous , refer if this logic can help

 

Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you for helping amitchandak . But the formula retrives the same result.

 

With previous formula

With your formula

The correct value that should be displayed is 01-04-2022 - 30-06-2022

 

Somehow we need the formula to count number of days between the dates in full months selected.

I found out i could make a datediff in months, but that would just return the number 3, and if i use that in the formula, it would only go 3 days back.

If that makes sense?