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

     

     

BetterCallFrank's avatar
BetterCallFrank
Resolver IV
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 :-)

 

 

greemlin's avatar
greemlin
Frequent Visitor
9 years ago

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

 

Again, thank you all,

Nicolas

 

  • BetterCallFrank's avatar
    BetterCallFrank
    Resolver IV
    9 years ago

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

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    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' )