CODEWITHSUNDEEP
powerbidax
dmonder
dmonder

Reputation: 392

Using DAX, how do I create a measure to sum a column from the most recent records so I can display the total by date?

I would like to take this table:

ID CID Term Date FTE
1 1 21SP 12/21/2020 1
1 2 21SP 12/21/2020 1
1 1 21SP 12/23/2020 0
1 2 21SP 12/23/2020 0
1 3 21SP 12/23/2020 1
1 4 21SP 01/01/2021 1
2 1 21SP 12/25/2020 1
2 2 21SP 12/25/2020 1
2 1 21SP 01/02/2021 0
3 1 21SP 01/02/2021 1

and turn it into a visualized table (and corresponding graph) like this:

Term Date FTE
21SP 12/21/2020 2
21SP 12/22/2020 2
21SP 12/23/2020 1
21SP 12/24/2020 1
21SP 12/25/2020 3
21SP 12/26/2020 3
21SP 12/27/2020 3
21SP 12/28/2020 3
21SP 12/29/2020 3
21SP 12/30/2020 3
21SP 12/31/2020 3
21SP 01/01/2021 4
21SP 01/02/2021 4

and so on...

Each row in the visualization is a sum of the FTE column for the most recent record up to that date, per ID, CID, and Term. So, for example, on 01/01/2021, the visualization is adding up the following records:

ID CID Term Date FTE
1 1 21SP 12/23/2020 0
1 2 21SP 12/23/2020 0
1 3 21SP 12/23/2020 1
1 4 21SP 01/01/2021 1
2 1 21SP 12/25/2020 1
2 2 21SP 12/25/2020 1

I also have a date table that has all the dates between the start and end dates of this table.

I am bumfuzzled. I am pretty sure I need to create a measure that starts like:

FTE by Date = 
VAR CurrentDate = SELECTEDVALUE( Dates[Date] )
...
RETURN
   ???

That is all I can muster at this point. My knowledge of DAX is nowhere near this level, I am afraid. Any help would be GREATLY appreciated. I am sure something like this has been asked before so point me in the right direction and I will go read some more.

Thanks.

Edit (2021-03-09): Added logic description per comment by @mkRabbani.

Upvotes: 0

Views: 121

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Let's first define a measure that will return the most recent date for a particular ID, CID, Term combination.

MostRecent =
VAR CurrentDate = SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[ID], Table1[CID], Table1[Term] ),
        Table1[Date] <= CurrentDate
    )

Using this measure, writing a FTE by Date measure becomes much easier:

FTE by Date = 
SUMX (
    FILTER ( ALLEXCEPT ( Table1, Table1[Term] ), Table1[Date] = [MostRecent] ),
    Table1[FTE]
)

enter image description here

Upvotes: 1

Related Questions