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.

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.