cancel
Showing results for
Did you mean: Regular Visitor

current period and prev. period productivity matrix

Hi,

I have the below data in power bi-

 Name Type Date Hours ABC Direct -1 7/27/2018 0:00 5.5 DEF Direct-2 7/30/2018 0:00 5.75 GHI Direct-3 7/31/2018 0:00 6.5 JKL Indirect-1 8/1/2018 0:00 2.5 ABC Indirect-2 8/2/2018 0:00 2 DEF Direct-3 8/3/2018 0:00 3 GHI Direct -1 8/6/2018 0:00 4.5 JKL Direct-3 8/7/2018 0:00 4

I need the output/report in the following form for ABC, DEF, GHI and JKL

 Direct -1 Direct-2 Direct-3 TOTAL DIRECT TOTAL INDRECT PRODUCTIVITY Current moth (Direct/(Direct+Indirect)*100 PRODUCtivity PREV. MONTH ABC 5.5 5.75 6.5 17.75 0 100

TIA

1 ACCEPTED SOLUTION  Community Support

Hi @isaideepika ,

At first, you need to pivot column "Type" with value "Hours" in the query editor. Then create a new column to get "YEAR&MONTH".

YM =
FORMAT ( test[Date], "yyyymm" )

Calculate the "totaldirect" ,"totalindirect" and the percentage.

totaldirect =
CALCULATE ( SUM ( test[Direct -1] ), ALLEXCEPT ( test, test[YM] ) )
+ CALCULATE ( SUM ( test[Direct-2] ), ALLEXCEPT ( test, test[YM] ) )
+ CALCULATE ( SUM ( test[Direct-3] ), ALLEXCEPT ( test, test[YM] ) )
totalindirect =
CALCULATE ( SUM ( test[Indirect-1] ), ALLEXCEPT ( test, test[YM] ) )
+ CALCULATE ( SUM ( test[Indirect-2] ), ALLEXCEPT ( test, test[YM] ) )
currentMonth =
( [totaldirect] / ( [totaldirect] + [totalindirect] ) ) * 100

You can get the previous values by PREVIOUSMOTH() or DATESMTD() function.

If this post helps, then please consider Accept it as the solution to help the other members find it.
2 REPLIES 2  Community Support

Hi @isaideepika ,

At first, you need to pivot column "Type" with value "Hours" in the query editor. Then create a new column to get "YEAR&MONTH".

YM =
FORMAT ( test[Date], "yyyymm" )

Calculate the "totaldirect" ,"totalindirect" and the percentage.

totaldirect =
CALCULATE ( SUM ( test[Direct -1] ), ALLEXCEPT ( test, test[YM] ) )
+ CALCULATE ( SUM ( test[Direct-2] ), ALLEXCEPT ( test, test[YM] ) )
+ CALCULATE ( SUM ( test[Direct-3] ), ALLEXCEPT ( test, test[YM] ) )
totalindirect =
CALCULATE ( SUM ( test[Indirect-1] ), ALLEXCEPT ( test, test[YM] ) )
+ CALCULATE ( SUM ( test[Indirect-2] ), ALLEXCEPT ( test, test[YM] ) )
currentMonth =
( [totaldirect] / ( [totaldirect] + [totalindirect] ) ) * 100

You can get the previous values by PREVIOUSMOTH() or DATESMTD() function.

If this post helps, then please consider Accept it as the solution to help the other members find it.  Super User

Check this example, here I have used one filter on past qtr and one on current Qtr. You can do the same for a month. One filter at a time.

Time dimension here has been marked as date tables

Sales Before QTR =
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))  