Getting the First Value Ignoring Month Slicer but Checking for Year Slicer

[giorajo]

I got this sales table

InvoiceDate

Client ID

Revenue

And from that have a measure that gets the monthly revenue by using SUM in the Revenue column from the table above and this how it looks

Client ID | Month/Yr | Revenue

    1     | May 2020 | 411

    1     | Apr 2020 | 222

    1     | Jun 2020 | 133

    1     | Jul 2020 | 432

    2     | Jan 2020 | 333

    2     | Feb 2020 | 322

    2     | Mar 2020 | 434

    3     | Feb 2020 | 132

    3     | Apr 2020 | 123

    3     | Aug 2020 | 434

I am having a problem creating the measure  get the value of the Revenue Total for the first month for each client ID whatever the value in the monthy slicer is:

If the user choses Aug 2020 the result should be like this:

Client ID | 1st Revenue | Current

    1     |     411     | blank

    2     |     333     | blank

    3     |     132     | 434

If the user choses Apr 2020 the result shoul be like this:

Client ID | 1st Revenue | Current

    1     |     411     | 222

    2     |     333     | blank

    3     |     132     | 123

The value for the 1st Revenue will only display if the selected value for the Year slicer is the same as the year of the 1st invoice date.

Thank you in advance!

[amitchandak]

@giorajo , is this raw data, or do you have a date in the raw data.

If these is no date create a date like this and join with date table

Date = "01 " & [Month/Yr]

and create a measure like

calculate(sum(Revenue), filter(all(Date), format(Date[date],"MMM YYYY") = format(min(Date[Date]),"MMM YYYY")))

select month measure would be

sum(Revenue)

[giorajo]

Thank you but I actually tried that already and the value 1st revenue is changed whenever the month slicer is changed. 

The table above shows the result of the statement you provided and the table below is the result I am expecting:

[Anonymous · Accepted solution]

Hi @giorajo ,

You can follow the below steps to achieve it:

1. Create a client table

Clients = VALUES('Table'[Client ID ])

2. Create a measure to get 1st revenue per client

1st Revenue = 
VAR _minMonth =
    CALCULATE (
        MIN ( 'Table'[Month/Yr] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Revenue] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] )
                && 'Table'[Month/Yr] = _minMonth
        )
    )

3. Create a measure to get current revenue per client

Current = 
CALCULATE (
    MAX ( 'Table'[Revenue] ),
    FILTER ( 'Table', 'Table'[Client ID ] = MAX ( 'Clients'[Client ID ] ) )
)

Best Regards

Rena

[giorajo]

Thank you, @amitchandak.  This is raw data. the table structure is Invoice Date | Company ID | Revenue.

I need to display the monthly totals for the revenue (should change depending on the month slicer) and the 1st revenue for the company's 1st month (always the same whatever the month is) where I am having problems.

[amitchandak]

@giorajo , Update the last post. please check if that can help