Not capturing values using dateadd

[nolan0716]

Hi,

So I was comparing the current value (Opportunity) and the value 3 months prior (PrevQuarterOPP) using the dateadd function but its not getting the correct value or should I say its not capturing the value. See screenshot below

When I checked the data source. I found out that Decision Period column changed inputs for the 2 months i'm comparing. The Decision Period changed from PD12 FY23 to PD6 FY24

Current Filter - Updated Month (January 2023)

Year - FY23, FY24

I wanted to display the value even if the Decision Period Change

[v-rzhou-msft]

Hi @nolan0716 ,

You can try code as below to create a measure.

PreviousQuarterOPP =
VAR _SELECTDATE =
    MIN ( 'TableName'[Updated Month] )
VAR _RangeStart =
    EOMONTH ( _SELECTDATE, -4 ) + 1
RETURN
    CALCULATE (
        SUM ( 'TableName'[Opportunity] ),
        FILTER (
            ALL ( 'TableName' ),
            'TableName'[Updated Month] >= _RangeStart
                && 'TableName'[Updated Month] < _SELECTDATE
        )
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nolan0716]

Hi @v-rzhou-msft 

this is what I got from your code

[amitchandak]

@nolan0716 , dateadd need continuous dates, so in case you need to add days, just add it to date

[Date] + 10

For month or year you can use Date function

Date(Year([Date]), Month([Date])+2 , Day([Date]) )

https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...

Dateadd and its Alternate: https://www.youtube.com/watch?v=9qiRivlBv8w&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=66