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

1 REPLY 1
Highlighted
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.

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 57 members 1,068 guests
Recent signins:
Please welcome our newest community members: