CALCULATION OF THE VALUE OF THE MONTHS BETWEEN TWO DATES

[eomedes]

Hi all,

I have a problem. As a source, I have for each project, a start date and the duration in months of each project, so it is easy to get the end date of each project.

The question is that I need to get the total invoiced each month.
It is very clear in the example below: I need to get the totals for each month (green row).

Thank you very much!

@PBCommunity @PBICommunity 

[v-janeyg-msft · Accepted solution]

Hi, @eomedes 

Accorind to your sample data and requirements, I made a sample file to meet them.

First you need to create a single date table to use the column as matrix column.

And use the [PROYECT] column as matrix row, then create measure as matrix value to display your desired result.

Like this:

Table 2 = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Month = FORMAT([Date],"MMM")

Monthnum = MONTH('Table 2'[Date])

In order to sort the months of the text type correctly, you need to sort by column first.

Like this:

Amount = 
IF (
    SELECTEDVALUE ( 'Table 2'[Monthnum] )
        >= MONTH ( SELECTEDVALUE ( 'Table'[START DATE] ) )
        && MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'Table'[END DATE] ) ),
    MAX ( 'Table'[$/MONTH] )
)

AmountTotal = 
IF (
    SELECTEDVALUE ( 'Table 2'[Monthnum] )
        >= MONTH ( SELECTEDVALUE ( 'Table'[START DATE] ) )
        && MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'Table'[END DATE] ) ),
    MAX ( 'Table'[$/MONTH] ),
    IF ( SELECTEDVALUE ( 'Table'[PROYECT] ) = BLANK (), SUMX ( 'Table', [Amount] ) )
)

Create [Amount] to display $/month then create [AmountTotal] using this measure for getting correct total.

Below is my sample.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Syndicate_Admin]

Hello, thank you for your contribution, I was putting it into practice but when an activity has a period of one year e.g. Start date = 8 March 2020 and end date = 7 March 2021, in the matrix it does not fill in all the rows, it only puts the amount each March per year and the other fields blank. How could I fix this detail?

Best regards.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result in a simple Table format.

[Syndicate_Admin]

Hi thanks for replying, I created the first formula:

Monthly payment =

IF (

HERSELFLECTEDVALUE ( 'Table 2'[Monthnum] )

>= MYTH ( SALTECTEDVALUE ( 'zC[Faith]cha_contrato] ) )

&& MAX ( 'Tabthe 2'[Monthnum] ) <= MOUNTAINH ( SHECTEDVALUE ( 'zContratos'[fecha_termino] ) ),

MAX ( 'zWith[sub_total] )

)

Then I continued with the second one for the total per contract and per month

TotalXMes =

IF (

SELECTEDVALUE ( 'Table 2'[Monthnum] )

>= MONTH ( SELECTEDVALUE ( 'zContracts'[fecha_contrato] ) )

&& MAX ( 'Table 2'[Monthnum] ) <= MONTH ( SELECTEDVALUE ( 'zContracts'[fecha_termino] ) )

MAX ( 'zContracts'[sub_total] ),

IF ( SELECTEDVALUE ( 'zContratos'[numcte] ) = BLANK (), SUMX ( 'zContracts', [Monthly Payment] ) )

)

I generated my matrix and in contracts that have the same month in start date and end date although different year it shows me balance only in the start and end months and not in the contract path

I'd like to think it's because the formula is designed to show the data per month and not over the life of the contract.

Thank you for your help.

Best regards.

[Ashish_Mathur]

Hi,

I will not be able to help unless you share what i requested with you in my previous message.

[eomedes]

Thank you very much!
It works perfectly!

[bcdobbs]

I went for a different approach from @amitchandak . You can make the DAX much easier by shaping your data into a form power bi works well with:

In Power Query I started with your source data:

1) Create a list of months in a not loaded query:

let
    Years = Table.FromList({2020..2022}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    LabelAsYear = Table.RenameColumns(Years,{{"Column1", "Year"}}),
    AddMonth = Table.AddColumn(LabelAsYear, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateDate = Table.AddColumn(ShowMonths, "Date", each #date([Year],[Month],1))
in
    CreateDate

2) Use this to generate a table with one row per project per month.
Effectively does a cross join so you have every project with every month and then filters out rows that aren't eligible.

let
    Source = #"Source Data",
    GetEndDate = Table.AddColumn(Source, "End Date", each Date.AddMonths([Start Date],[Months])),
    CrossJoin = Table.AddColumn(GetEndDate, "Custom", each MonthList),
    GetAllMonths = Table.ExpandTableColumn(CrossJoin, "Custom", {"Date"}, {"Date"}),
    ChangedType = Table.TransformColumnTypes(GetAllMonths,{{"Date", type date}, {"End Date", type date}}),
    AddEligbilityCheck = Table.AddColumn(ChangedType, "Eligible", each 
        if [Date] >= [Start Date] and [Date] <= [End Date]
        then 1
        else 0),
    FilterToEligble = Table.SelectRows(AddEligbilityCheck, each ([Eligible] = 1)),
    RemovedOtherColumns = Table.SelectColumns(FilterToEligble,{"Date", "$/Month", "Project"})
in
    RemovedOtherColumns

3) In your data model it's easier if you add a basic calendar table.

4) Because your data is now at the month granularity you can get your results just using normal power bi visuals and little/no DAX:

Solution available here .

[amitchandak]

@eomedes , refer if one of my two blogs can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...