DAX Measure help based on date logic

[GarryFarrell]

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.

[Anonymous · Accepted solution]

Garry,

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

Selected Date =
IF ( COUNTROWS ( 'Date' ) = 1, FIRSTDATE ( '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.