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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nolan0716
Regular Visitor

Not capturing values using dateadd

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

 

nolan0716_1-1675330319531.png

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

nolan0716_2-1675330377139.png

 

 

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

 

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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.

Hi @v-rzhou-msft 

this is what I got from your code

 

nolan0716_0-1675413242453.png

 

amitchandak
Super User
Super User

@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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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