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
isaideepika
Regular Visitor

current period and prev. period productivity matrix

Hi,

I have the below data in power bi-

NameTypeDateHours
ABCDirect -17/27/2018 0:005.5
DEFDirect-27/30/2018 0:005.75
GHIDirect-37/31/2018 0:006.5
JKLIndirect-18/1/2018 0:002.5
ABCIndirect-28/2/2018 0:002
DEFDirect-38/3/2018 0:003
GHIDirect -18/6/2018 0:004.5
JKLDirect-38/7/2018 0:004

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

 

 Direct -1Direct-2Direct-3TOTAL DIRECTTOTAL INDRECTPRODUCTIVITY Current moth (Direct/(Direct+Indirect)*100PRODUCtivity PREV. MONTH
        
ABC5.55.756.517.750100 

 

Can you please help.

 

TIA

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @isaideepika ,

 

At first, you need to pivot column "Type" with value "Hours" in the query editor.

1-1.PNG

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.

 

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

View solution in original post

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

Hi @isaideepika ,

 

At first, you need to pivot column "Type" with value "Hours" in the query editor.

1-1.PNG

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
amitchandak
Super User
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 = 
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))

 

 

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.