Total value from the first day in a slicer range

[Woof19]

Good Morning

I'm trying to get the total of an amount column for the first day in a slicer range. This is part of a larger measure which is calculating a percentage increase from first to last, however if I can calculate the first I'm sure I can get the rest to work.

When I run the below measure, I get the total for the whole date range and not just the first date.

CALCULATE(SUM('UT Pricing - CONS'[Full Bid Net Asset Value]),

FILTER(ALLSELECTED('UT Pricing - CONS'),MIN('UT Pricing - CONS'[Price Date]))

)

Any ideas welcome!

[sturlaws · Accepted solution]

Hi @Woof19 ,

the ALLSELECTED is a function which can be quite hard to get the grasp of: The definitive guide to ALLSELECTED 

Could you check if this DAX resolves your issue?

CALCULATE (
    SUM ( 'UT Pricing - CONS'[Full Bid Net Asset Value] ),
    FILTER (
        ALL ( 'UT Pricing - CONS' ),
        'UT Pricing - CONS'[Price Date] = MIN ( 'UT Pricing - CONS'[Price Date] )
    )
)

If you have filters you want to keep, you can use the ALLEXCEPT-function

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Woof19]

This works to the extent that it does calculate the value, however when this measure is put into a table / graph it returns the value for each individual day. I want it to return the value for the first day in the slicer for all days. i.e. for the calendar year 2019, on 3 June I want to have the value for 1 January. At present, on 3 June I can only get the total for 3 June.

I know this seems odd, however the next step (which I can do!), is to get the value for the relevant day and calculate the movement between the first date and each date in the slicer range - effectively giving me the "growth" since the start of the slicer period.

[sturlaws]

working a bit in the blind here, could you try this:

VAR _firstDayOfYear =
    DATE ( YEAR ( MIN ( 'UT Pricing - CONS'[Price Date] ) ), 1, 1 )
RETURN
    CALCULATE (
        SUM ( 'UT Pricing - CONS'[Full Bid Net Asset Value] ),
        FILTER (
            ALL ( 'UT Pricing - CONS' ),
            'UT Pricing - CONS'[Price Date] = _firstdayofyear
        )
    )

[Woof19]

That does return the beginning of the year. However I want to try and get the first date in the slicer range which might not necesarily be the beginning of the year. The below, in theory, would give the answer but when I put the measure into a table it generates the date as split in the table, not the first day in the slicer range.

Z Date test = DATE ( YEAR ( MIN ( 'UT Pricing - CONS'[Price Date] ) ), MONTH( MIN ( 'UT Pricing - CONS'[Price Date] ) ), DAY ( MIN ( 'UT Pricing - CONS'[Price Date] ) ) )

In the below, I want to get 18/4/19 for each row, not just the first one. I can then use this as the base to calculate the movement from 18/4/19 to each day in the table.