current period and prev. period productivity matrix

[isaideepika]

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

Can you please help.

TIA

[v-eachen-msft · Accepted solution]

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.

[amitchandak]

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 = 
CALCULATE(SUMx(FILTER(sales,and(Sales[Order_Date]>= STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY)) && Sales[Order_Date]<= DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY),
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))