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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.