Building a "contribution margin" report, need help on some calculations approach

[Anonymous]

Hello community,

DAX is far from native for me so i may be missing some important concepts or maybe going in completely wrong direction 🙂

What i try to get is a kind of Contribution report where i would like to know when project was delivered (income is recorded to that period), hom much time was recorded specifically in the "delivery" period and to see total and estimate times (though you can not see estimate in the result anywhere). So i am just going step by step and experementing 🙂 And currently i am stuck at:

• How to calculate if delivery date is in the period i am looking on? Period is "calculated", its PBI Date Hierarchy?
• Total project time in the "In Between" - how to "accumulate" it in PBI?
• FInal version - honestly not sure how to achieeve it at all yet 🙂 So any suggestions welcome.

Thanks in advance!

[ThomasWeppler · Accepted solution]

Hi Igovar

The first thing I would do would be to build at date table.

 

You go to DATA and add new table and type with this dax code:

Calendar =

  GENERATE (

    CALENDAR ( DATE ( 2019, 1, 1 ), TODAY()),

    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR quarters = QUARTER(currentDay)

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowQuarter = QUARTER( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

    "Quarter", quarters,

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "quarter index", INT( (years - nowYear ) * 4 + quarters - nowQuarter ),

    "year index", INT( years - nowYear )

  ))

Next add a new colum to the table and type something like this in:

Months_year = FORMAT(Kalender[Date], "mmm-yy")

Sort the month_year colum after the month index colum.

When that is done you will just have to connet the calendar tables date to timespent table.

in Model

after that you go to Reports and choose a visual you like and put the Month_year option in the X-axis and the time spent(Hrs) in the y option.

This will get you going and from here you can pretty much build the rest by connection the right tabels in Model and adding them in Reports.

I hope this post helps you.

[Anonymous]

Thanks, i will try it out.