cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
greemlin Frequent Visitor
Frequent Visitor

Year to date average

Hi,

I'm a recent user of PowerBI, and I have a question to build a report based on the Billable activities VS non Billable activities

I've got the source as follow with the date of the activity, the duration of it, and whether it is a Customer Service, or internal, etc. activity type.

 

Capture d’écran 2016-12-28 à 16.29.51.png

 

I've just regrouped the my activities on the Billable ones, vs the Non Billable ones, to get the following report.

Capture d’écran 2016-12-28 à 16.20.04.png

Now, i'd like to display a line showing the Year to Date average of Billable part.

It means:

  • in January, i'd like it to show 43.60%,
  • in February, it should show (43.60+43.22)/2 = 43.41%
  • in March, it should show (43.60+43.22+60.99)/3 =  49.27%

I don't know how to do that with PowerBI.
If anyone can help, that would be great.


Thank you,

Nicolas

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Year to date average

Hey Nicolas,

 

this apporach should work fine for you:

 

1) create a Date table without "holes" - prerequisite for the time intelligence to work properly

    easiest way is to create a calculated table with the pattern

Date = CALENDAR( MIN( YourData[Start Date] ), MAX( YourData[Start Date] ) )

2) create a relationship between your data and the new date table

 

3) create a Measure that calculates the percentage for a given month:

  for example:

Duration Billable Pct = 
DIVIDE(
  CALCULATE(
    SUM( YourData[Duration] ),
    YourData[Category] = "billable"
  ),
  CALCULATE(
    SUM( YourData[Duration] ),
    ALL( YourData[Category] )
  )
)

 

4) create a Measure that will give you the YTD/Running total of that Percentage, for example:

  

Duration Billable Pct YTD = TOTALYTD( [Duration Billable Pct], 'Date'[Date] )

 

Should result in sth like this:

Screenshot at Dez. 28 17-56-15.png

 

 

Give it a try and let me know if it helps :-)

 

 

6 REPLIES 6

Re: Year to date average

Hey Nicolas,

 

this apporach should work fine for you:

 

1) create a Date table without "holes" - prerequisite for the time intelligence to work properly

    easiest way is to create a calculated table with the pattern

Date = CALENDAR( MIN( YourData[Start Date] ), MAX( YourData[Start Date] ) )

2) create a relationship between your data and the new date table

 

3) create a Measure that calculates the percentage for a given month:

  for example:

Duration Billable Pct = 
DIVIDE(
  CALCULATE(
    SUM( YourData[Duration] ),
    YourData[Category] = "billable"
  ),
  CALCULATE(
    SUM( YourData[Duration] ),
    ALL( YourData[Category] )
  )
)

 

4) create a Measure that will give you the YTD/Running total of that Percentage, for example:

  

Duration Billable Pct YTD = TOTALYTD( [Duration Billable Pct], 'Date'[Date] )

 

Should result in sth like this:

Screenshot at Dez. 28 17-56-15.png

 

 

Give it a try and let me know if it helps :-)

 

 

tringuyenminh92 New Contributor
New Contributor

Re: Year to date average

Hi @greemlin,

 

Cause i'm so sleepy(as Vietnam timezone) Smiley Very Happy, so please try my workaround solution like this:

(I just consider this as workaround solution cause i use calculated column and fix in year scenario)

 

  • Group by Month and sum bill & non-bill 

 

B = SUMMARIZE(Activities,Activities[Month],Activities[MonthInt],"Bill",CALCULATE(sum(Activities[Activity Duration]),Activities[ActivitySimplied]="Others"),"Non",CALCULATE(sum(Activities[Activity Duration]),Activities[ActivitySimplied]<>"Others") )

Now you have data same as picture: 

  • Calculated column Total: 
    total = B[Bill] + B[Non]

Screenshot 2016-12-29 01.08.10.png

  • Create Dates table: Dates= Calendarauto()
  • Create calculated column for % Bill:
% bill = DIVIDE(B[Bill],B[total])
  • Create calculated column for % non-Bill:
% non-bill = DIVIDE(B[Non],B[total])
  • Add Date column to this table to use Time Pattern for YTD:
Date = date(2016,B[MonthInt],1)
  • Ensure making relationship between B table and Dates: 

Screenshot 2016-12-29 01.17.29.png

 

  • Create calculated measure YTD bill & non-bill
YTD - Bill = CALCULATE(sum(B[% bill])/MAX(Activities[MonthInt])  ,filter(ALL(Dates),Dates[Date]<=MAX(Dates[Date])  ))
YTD - Non-Bill = CALCULATE(sum(B[% non-bill])/max(Activities[MonthInt]) ,filter(ALL(Dates),Dates[Date]<=MAX(Dates[Date])  ))

Screenshot 2016-12-29 01.12.11.png

 

Sample pbix , sample data  ^_^ gud9 ^_^

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

Highlighted
greemlin Frequent Visitor
Frequent Visitor

Re: Year to date average

Hi

 

Thank you for the fast reply.

It works perfectly.


I'll still try to understand the second method suggested to me for my knowledge, I'll also let you know if I succeed Smiley Happy

 

Again, thank you all,

Nicolas

 

Re: Year to date average

Hi Nicolas,

 

in order to not use ALL you can write

Duration Billable Pct = 
DIVIDE(
  CALCULATE(
    SUM( 'MOCK_DATA csv'[Duration] ),
    'MOCK_DATA csv'[Category] = "billable"
  ),
  CALCULATE(
    SUM( 'MOCK_DATA csv'[Duration] ),
    OR('MOCK_DATA csv'[Category] = "billable", 'MOCK_DATA csv'[Category] = "non billable" )
  )
)

HTH,

Frank

 

Please mark as solution and send Kudos if you learned a thing or two Smiley Happy

tringuyenminh92 New Contributor
New Contributor

Re: Year to date average

Hi @greemlin,

 

You could remove not related group in Group by step:

 

 

B = SUMMARIZE(Activities,Activities[Month],Activities[MonthInt],"Bill",CALCULATE(sum(Activities[Activity Duration]),Activities[ActivitySimplied]="Others" && [groupcolumn] <> 'filtergrouphere'),"Non",CALCULATE(sum(Activities[Activity Duration]),Activities[ActivitySimplied]<>"Others" && [groupcolumn]='filtergrouphere' ) 

 

 

 

DivyahsreeM Visitor
Visitor

Re: Year to date average

Hello,

Am very new to Power BI and i got requirement to calculate YTD. i followed the same steps to calculate YTD.. in my Table i have three columns(two columns are unpivoted columns, like success, error, all respective vaues for the same) Other column is list of months.. i have to calculate YTD of Vaues..  
i have craeted one Date Table with one column which contains date frmo 01/01/2017 to 31/12/2017.
then i created one calculated column which is of the data type date respectively for each month, and i hvae careted the relationship between them,

now i have to create the Measure, 
requirement is just need to add the values of different attribute respective of month and its previous months value..
am bit confused how to calculate the measure

Duration Billable Pct = 
DIVIDE(
  CALCULATE(
    SUM( YourData[Duration] ),
    YourData[Category] = "billable"
  ),
  CALCULATE(
    SUM( YourData[Duration] ),
    ALL( YourData[Category] )
  )
)