Forum Discussion

Paul_Petro's avatar
Paul_Petro
New Member
3 years ago
Solved

Creating a measure using 2 dates based on if

I use this measure to calculate a past date in PBI for comparison purposes.
 
Old_Date = calculate([Total sales], DATEADD(Dates[Date],-1099,DAY))
 
As I am new to PBI I am having trouble with writing a measure that allows me to have a possible of 2 previous dates based on criteria. What I am looking for is something like this:
 
old_date = if(month(Dates[Date])>=10, calculate([Total sales], DATEADD(Dates[Date],-1099,DAY)), calculate([Total sales], DATEADD(Dates[Date],-1463,DAY)))
 
Firstly, is this possible? Secondly by using the if statement PBI does not let me select Date[Date].

 

I should add that these are tables stored on a server.

Appreciate any help.

 

Thanks

 

I have tried various versions of the measure above.

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Paul_Petro , You have use max on date of date

    Try like

    old_date = if(month(max(Dates[Date]))>=10, calculate([Total sales], DATEADD(Dates[Date],-1099,DAY)), calculate([Total sales], DATEADD(Dates[Date],-1463,DAY)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Paul_Petro , You have use max on date of date

    Try like

    old_date = if(month(max(Dates[Date]))>=10, calculate([Total sales], DATEADD(Dates[Date],-1099,DAY)), calculate([Total sales], DATEADD(Dates[Date],-1463,DAY)))

  • Paul_Petro's avatar
    Paul_Petro
    New Member
    3 years ago

    This is great and it works a treat with the exception in 1 week which is split, 31/12/2022 and 1/1/2023 fall in this same week.  Using max applies the same offset to the whole week, where ideally it should apply -1099 to the dates in 2022 and -1463 to the dates in 2023.  Not sure how I would get around this simply.