cancel
Showing results for
Did you mean:
Highlighted
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.

1 ACCEPTED SOLUTION

Accepted Solutions
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.

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.