Calculate monthly totals with a start date and end date meeting a particular criteria?

[Jotten]

I apologise if this has already been solved but I can not seem to find the correct solution that works for me.  I am trying to calculate future planned cost for Capital and Expense.  The table I have has an amount (Planned Cost) with a start and end date associated with it.

I need to determine what the amount would be per month for each Portfolio.  I have 11 portfolios and there are multiple entries per portfolio.  The other twist is I need to determine if it is Capital or Expense. I have a column called "Billing Override".  If it is capital it will have "IS CER" in the "Billing Override" column if blank it is Expense.

I also have a "Calendar" table and a table called "PortfolioID".  Here is what I have so far.  I created a table which spread the dates by day.  I can't seem to get the relationship to work.  Maybe there is a better way to do this?  Please if you can help it would be much appreciated

Original data table

Table spreading the Start and End dates

[Anonymous]

Please send pbix file if pissible.

[Anonymous]

How did you do the second table with the dates? trying to do something similar but only on EOM dates between two dates

[v-yulgu-msft]

Hi @Jotten,

Please refer to below DAX formulas to create calculated tables:

CrossJoin = ADDCOLUMNS(FILTER(CROSSJOIN(Table1,'Date Dim') ,[Date].[Year]>=Table1[Start On].[Year]&&[Date].[MonthNo]>=Table1[Start On].[MonthNo]&&[Date].[Year]<=Table1[Due On].[Year]&&[Date].[MonthNo]<=Table1[Due On].[MonthNo]),"Year-Month",YEAR([Date])&"-"&[Date].[Month])

Result Table =
SUMMARIZE (
    FILTER (
        'CrossJoin',
        'CrossJoin'[Billing Override] = "IS CER"
            || 'CrossJoin'[Billing Override] = BLANK ()
    ),
    'CrossJoin'[Project:Portfolio ID],
    'CrossJoin'[PortfolioID],
    'CrossJoin'[Year-Month],
    'CrossJoin'[Billing Override],
    "Amount", SUM ( 'CrossJoin'[Plannned Cost] )
)

Best regards,

Yuliana Gu

[Jotten]

Hi Yuliana Gu,

I cannot seem to get the crossJoin table to work.  Is this joining the two tables I gave an example of or is this joining my first table (Data Source) with my Calendar table?  I do not have any measures in my calendar table.  I only have calculated columns.

In the above crossjoin table - 'Table' is the table I created to spread the Start and End Dates. Should I use the 'Data Source' table?  I tried it both ways and neither seemed to work.

Below is 'Table'

'Table' Created from Data Source to spread the planned cost .'Data Source' Need to spread the Planned Cost column across the Start On and Due On dates. End result - I will be able to calculate the cost per month.

The above table 'Data Source' will be pulled in every month.  I would like to calculate the planned cost per month.  If it is Capital it will have "IS CER" in the Billing Override column and if it is Expense the Billing Override column will be empty.  In addition to these two tables above I have created a 'Calendar' table.

Thank you so much for your help.  

Jodie

[v-yulgu-msft · Accepted solution]

Hi @Jotten,

---

I cannot seem to get the crossJoin table to work.  Is this joining the two tables I gave an example of or is this joining my first table (Data Source) with my Calendar table?  I do not have any measures in my calendar table.  I only have calculated columns.

  

---

You should CROSSJOIN the first table (Data Source) with calendar table.

Best regards,

Yuliana Gu

[Jotten]

Thank you I did get this to work.  However, in checking the numbers I realized the Cost is not being spread accurately.  The sum of "planned cost" is much larger then the sum of "planned cost" in my original data source.   I feel I am back to the begining.  I need to spread my "Planned Cost" between a Start date and an End Date.  

Thank you