find penultimate (last -1) date in the table

[Foolke]

All,
I have the following mesure to extract the last date from a table: 

INC last date = CALCULATE(sum(Tickets[INCIDENT]);LASTDATE(Tickets[TicketDate]))

I want to compare the value (number of tickets) of this date with the number of tickets of the penultimate date in the table.

• Which measure can I use to find the penultimate date?
• Which visual could I use for this? Iwant something like in screenshot (excel) but in Power BI.
  screenshot

Thx

[parry2k]

@Foolke can you share sample data with expected output?

[Foolke]

@parry2k 

Data:

TicketDate	Responsible Group	INCIDENT	SRQ
04/12/2019	Westteam	253	100
25/11/2019	Westteam	239	120

example output from excel

[v-shex-msft]

Hi @Foolke ,

You can refer to the following steps if they meet for your requirements.

Steps:

1. Enter to query editor and do unpivoted columns on value fields incident SRQ.

Unpivot columns (Power Query) 

2. Create a matrix visual with attribute as row, ticketDate as column.

3. Write a measure formula to use current attribute and date to calculate the dynamic results and use on matrix value field.

Measure =
IF (
    ISFILTERED ( 'Sample'[TicketDate] ),
    CALCULATE ( SUM ( 'Sample'[Value] ), VALUES ( 'Sample'[Attribute] ) ),
    VAR _max =
        CALCULATE ( MAX ( 'Sample'[TicketDate] ), VALUES ( 'Sample'[Attribute] ) )
    VAR _previous =
        CALCULATE (
            MAX ( 'Sample'[TicketDate] ),
            FILTER ( ALLSELECTED ( 'Sample' ), [TicketDate] < _max ),
            VALUES ( 'Sample'[Attribute] )
        )
    RETURN
        CALCULATE (
            SUM ( 'Sample'[Value] ),
            FILTER ( ALLSELECTED ( 'Sample' ), [TicketDate] = _max ),
            VALUES ( 'Sample'[Attribute] )
        )
            - CALCULATE (
                SUM ( 'Sample'[Value] ),
                FILTER ( ALLSELECTED ( 'Sample' ), [TicketDate] = _previous ),
                VALUES ( 'Sample'[Attribute] )
            )
)

4. Modify subtotal -> column total to change display aggregate field name.

Regards,

Xiaoxin Sheng

[Foolke]

Thx @v-shex-msft , The unpivot table does a part of the job.

The only remaining challenge is to select the last 2 values from the dates.

Dates are updated every week. I am only interested in the last 2 dates. 

[v-shex-msft]

Hi @Foolke ,

Do you mean your table contains a lot of records but you only want to display the last two dates? If this is a case, you can add a visual level filter on date fields with 'TopN' mode to choose 'top' 2 dates.

Regards,

Xiaoxin Sheng

[Foolke]

Thx @v-shex-msft , we are getting there.

Now I have a last problem. 

How can I make sure that the 2 columns are subtracted instead of summing (column a - column b)? If I do this via agregation it doesn't work. See screenshot

[v-shex-msft]

HI @Foolke,

I don't think you can achieve this via aggregation features. In my opinion, I'd like to suggest you create a new calculated column to store the result of 'column A'- 'Column B'.

Regards,

Xiaoxin Sheng