Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
thank you, Herbert!
that was exactly what I needed
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |