Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jolyon
Helper III
Helper III

how to compare amount of values Actual vs Previous year?

Hi everyone,

I need your help please.

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

table.png

 

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):
compare.png

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!

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@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.

how to compare amount of values Actual vs Previous year_1.jpg

 

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.

how to compare amount of values Actual vs Previous year_2.jpghow to compare amount of values Actual vs Previous year_3.jpg

 

Best Regards,

Herbert

View solution in original post

2 REPLIES 2
v-haibl-msft
Employee
Employee

@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.

how to compare amount of values Actual vs Previous year_1.jpg

 

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.

how to compare amount of values Actual vs Previous year_2.jpghow to compare amount of values Actual vs Previous year_3.jpg

 

Best Regards,

Herbert

thank you, Herbert!
that was exactly what I neededSmiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.