cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GarryFarrell Regular Visitor
Regular Visitor

DAX Measure help based on date logic

Hi all,

 

I have a PBIX file and I need to create a measure that is based on other date columns. I need a measure that Sums the Amount column based of my Date Slicer on the report. The date slicer will be slicing a month. ie Oct-2016

 

I need to sum the amount column when the select month is between the Contract Data and the Offer Date and less than the Projected Start Date.

 

Projects not Started = Sum(Amount if [Offer Date] between [Project Start Date] and SelectMonth and [Projected Start Date] > SelectedMonth)

 

So using the sample data below.

If the Slicer is set to Oct-2016 then project 1 has not started based on the projected start date and the measure should equal 4. For project 2 also using Oct-2016 the projected start date is Sep-2016 and the project has already start so the measure should equal 0.

 

SampleData.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Steve_Wheeler Established Member
Established Member

Re: DAX Measure help based on date logic

Garry,

Assuming you create a measure to get the Selected Date from your slicer:

 

Selected Date =
IF ( COUNTROWS ( 'Date' ) = 1FIRSTDATE ( 'Date'[Selected Month] )BLANK () )

 

 I think you can then create a measure to SUMX the Amount on a filtered Projects table:

 

Sum Amount =
IF (
    ISBLANK ( [Selected Date] ),
    BLANK (),
    SUMX (
        FILTER (
            Projects,
            Projects[Offer Date] >= Projects[Project Start Date]
                && Projects[Offer Date] <= [Selected Date]
                && Projects[Projected Start Date] > [Selected Date]
        ),
        Projects[Amount]
    )
)

 

Note that I'm not quite sure how you want to handle the Month from the filter against the Dates in teh table - above sample will include Projects with [Projected Start Date] of 2/10/2016 if the slicer uses the 1st of each month.

View solution in original post

1 REPLY 1
Highlighted
Steve_Wheeler Established Member
Established Member

Re: DAX Measure help based on date logic

Garry,

Assuming you create a measure to get the Selected Date from your slicer:

 

Selected Date =
IF ( COUNTROWS ( 'Date' ) = 1FIRSTDATE ( 'Date'[Selected Month] )BLANK () )

 

 I think you can then create a measure to SUMX the Amount on a filtered Projects table:

 

Sum Amount =
IF (
    ISBLANK ( [Selected Date] ),
    BLANK (),
    SUMX (
        FILTER (
            Projects,
            Projects[Offer Date] >= Projects[Project Start Date]
                && Projects[Offer Date] <= [Selected Date]
                && Projects[Projected Start Date] > [Selected Date]
        ),
        Projects[Amount]
    )
)

 

Note that I'm not quite sure how you want to handle the Month from the filter against the Dates in teh table - above sample will include Projects with [Projected Start Date] of 2/10/2016 if the slicer uses the 1st of each month.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 57 members 1,068 guests
Please welcome our newest community members: