Grouping by period number calculated from start date


#1

I was hoping that someone may be able to point me in the right direction.
I have a list of projects each with a different start dates and an expenses table. I can already get the expenses per month based on when they occur with the following formula.

Expenses = 
VAR CurrentItem = SELECTEDVALUE( 'Forecast Method'[Method] )
var duedate = CALCULATE(SUM('Job Cost Detail'[Amount]),FILTER('Job Cost Detail','Job Cost Detail'[Expense]="Cost"),USERELATIONSHIP('Calendar'[Date],'Job Cost Detail'[DueDate]))
var paidDate = CALCULATE(SUM('Job Cost Detail'[Amount]),FILTER('Job Cost Detail','Job Cost Detail'[Expense]="Cost" && 'Job Cost Detail'[PaidDate] <> BLANK()),USERELATIONSHIP('Calendar'[Date],'Job Cost Detail'[PaidDate]))
var invDate =  CALCULATE(SUM('Job Cost Detail'[Amount]),FILTER('Job Cost Detail','Job Cost Detail'[Expense]="Cost"),USERELATIONSHIP('Calendar'[Date],'Job Cost Detail'[InvDate]))
return SWITCH(TRUE(),
CurrentItem = "Cash Flow", paidDate,
CurrentItem = "Due", duedate,
CurrentItem = "Incurred", invDate,
invDate)

But I was hoping to do was take the start date from each project and determine the period number based on the start date of the project and group by the period number

59%20am

If it is overcomplicated by the formula I am using to calculate the expenses I can change it

Any help would be greatly appreciated


#2

Check out the particular technique here.

This needs to be solved in the data model and with DAX.

The formula technique is called ‘Events in Progress’. Is solves this exactly. Just have to follow the key ideas that a reviewed here.

See how you go with this.

Chrs
Sam


#3

Hi Sam, thanks for your help with this. I was able to get it to work