SUM all rows whilst using a filter

AltGr9 — Fri, 07 Jun 2019 14:41:33 GMT

Hello,

I have a dataset that looks like this:

Project	Date	Amount
A	2019-06-06	5
A	2019-06-07	10
B	2019-06-05	2
B	2019-06-07	3
C	2019-06-07	2

What I want to do, is as follows:

I want the user to be able to select a project, and see a breakdown of ALL projects and the sum of the amount for each project. So far, so easy. But where I'm struggling is, I need it to only sum the amount for each project, where the amounts fall within the MIN and MAX dates of the selected project.

So, for example, if the user selected Project A, we'd get this:

Project	Project Breakdown Amount
A	15
B	3
C	2

Earliest date in project A: 2019-06-06
Latest date in project A: 2019-06-07

Note that the amount for Project B should be 3, not 5, because, although the sum total for Project B (2+3) is 5, the 2 amount was recorded on June 5, which is outside of the date range of the selected project (i.e. June 6 to June 7)

The key is that the min and max dates for the selected project should provide the filter context for the sum of the Amounts for all projects. It must be dynamic, in other words, set by the user (selecting the project).

This is what I have tried

I have tried creating the following measure, but it didn't work:

Project Breakdown Amount = 
VAR EarliestDateOfSelectedProject = MIN( [Date] )
VAR LatestDateOfSelectedProject = MAX( [Date] )
RETURN
CALCULATE(    SUM( [Amount] )
            , [Date] >= EarliestDateOfSelectedProject 
            , [Date] <= LatestDateOfSelectedProject 
            , ALL( [Project] )
         )

This returns me the total amount of all the projects between the dates, set by the user's Project selection, but it doesn't allow me to break it down by Project, if I include the [Project] column in the table.

Here is what it give me when the user selects Project A:

Project selected by user: A

Earliest date of project: 2017-06-06

Latest date of project: 2017-06-07

Project	Project Breakdown Amount
A	20

This isn't what I want. I want it to return the table I gave above. But it won't do it.

Can anyone help me achieve the desired result?

Thank you.

Re: SUM all rows whilst using a filter

Anonymous — Fri, 07 Jun 2019 18:24:17 GMT

Here's how I accomplished this:

Created a Date table and related that to your main table
Created two project tables, one related to the table and one not

Use the column from the "DiscConnProject" table for your slicer

use the project column from the project table for rows on your table

The following measures collect the min and max of the project selected:

FirstDate of Selected = 
CALCULATE(
    FIRSTDATE( 'Date'[Date] ),
    FILTER( 
        ALL( Table1),    
        SELECTEDVALUE(DiscConnProject[Project] ) = Table1[Project]
    )
)

LastDate of Selected = 
CALCULATE(
    LASTDATE( 'Date'[Date] ),
    FILTER( 
        ALL( Table1),    
        SELECTEDVALUE(DiscConnProject[Project] ) = Table1[Project]
    )
)

then a simple total:

Total Amt = SUM ( Table1[Amount] )

and the last measure:

Measure = 
CALCULATE( 
    [Total Amt],
        FILTER( 
            ALL ('Date'[Date]),
       'Date'[Date] >= [FirstDate of Selected]
       && 'Date'[Date] <= [LastDate of Selected]   
        )
    ,
    ALL( DiscConnProject)
)

topic Re: SUM all rows whilst using a filter in DAX Commands and Tips

SUM all rows whilst using a filter

Re: SUM all rows whilst using a filter