how to compare amount of values Actual vs Previous year?

[Jolyon]

Hi everyone,

I need your help please.

I have the following table with the orders from various years:

1)Now we want to compare the amount of orders from Actual Year(i.e.if I open my report today, then it will be 2016) and the Previous Year(in this case 2015) and present it after months(from January on):

where green and black bars present amount of orders from 2016 and 2015.

2)and as a next step I want to present:

- Number of orders,that have "spent time" > 30 days as a number

- List of orders that have "spent time" > 30 days as table

Filter after month/year

I assume, I should make a measure here, but how could I present WHERE-clause?

thanks a lot for advice!

[v-haibl-msft · Accepted solution]

@Jolyon

In this scenario, we can first create another table which only includes current and previous years’ order information with following formula.

Table2 =
SUMMARIZECOLUMNS (
    Table1[Order],
    Table1[spent days],
    Table1[Open Date_Year],
    Table1[Open Date_Month],
    FILTER ( Table1, Table1[Open Date_Year] >= YEAR ( TODAY () ) - 1 )
)

Then we need to create another Month Table to sort the month name in X-Axis as beow.

Create a measure to display number of orders that have "spent time" > 30 days.

Order_Num_Above30 = 
CALCULATE (
    DISTINCTCOUNT ( Table2[Order] ),
    FILTER ( ALLSELECTED ( Table2[spent days] ), Table2[spent days] > 30 )
)

No we only need to drag columns and measure into chart as below. We can use visual level filters to display List of orders that have "spent time" > 30 days.

I’ve also uploaded my PBIX file here for reference.

Best Regards,

Herbert

[Jolyon]

thank you, Herbert!
that was exactly what I needed