Forum Discussion

greemlin's avatar
greemlin
Frequent Visitor
9 years ago
Solved

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, ...
Need Help
  • BetterCallFrank's avatar
    BetterCallFrank
    9 years ago

    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:

     

     

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

     

     

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi greemlin,

 

Cause i'm so sleepy(as Vietnam timezone) :smileyvery-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]

  • 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: 

 

  • 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])  ))

 

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