topic Value spread over a date range in DAX Commands and Tips

Value spread over a date range

Anonymous — Wed, 23 Sep 2020 12:30:54 GMT

Hello good afternoon.

I'm finding it difficult to find a measure that brings me a value divided over a range of dates, based on just one row in a fact table. I explain: I have the following data:

Document           StartData           EndDate          MonthCount   Amount    Percentual of Amount                 Apportioned Amount

X                         01/01/2020          12/31/2020     12                 1200             25%                               300

X1                       01/01/2020          12/31/2020     12                 1200             25%                               300
X2                       01/01/2020          12/31/2020     12                 1200             25%                               300

X3                       01/01/2020          12/31/2020     12                  1200            25%                               300

The table above is just an example, but the data may vary in months, percentage of apportionment, prorated amount, etc. From this table I need to visualize each line, for an interval of 12 months the prorated amount. I intend to see it this way, in a Power Pivot

Document          01-2020          02- 2020       03-2020   ......    01-2021        02-2021

X               25                 25             25       ......       25              0

The value in the "Prorated Value" column needs to be distributed over the 12-month interval between the "Start Date" and "End Date" dates. In addition, another rule is that the prorated amount below 10,000 per month must be viewed directly in the month of the date in the "Start Date" column.

I have the following measure:

VAR StartDate= IF( HASONEVALUE('Tabela4'[StartDate]) ; VALUES(Tabela4[StartDate]))
VAR EndDate = IF(HASONEVALUE(Tabela4[EndDate) ; VALUES(Tabela4[EndDate]))
VAR DataCalendario = IF(HASONEVALUE('Calendário'[Date]) ; VALUES('Calendário'[Date]))
VAR ApportionedAmount = SUMX(Tabela4;[Apportioned Amount])
VAR ApportionedValor = DIVIDE(ApportionedAmount ; SUM(Tabela4[MonthCount]))

RETURN
IF( ApportionedAmount > 120000 ;
IF( MAX('Calendário'[Date]) <= DataFinal && MAX('Calendário'[Date]) >= StartDate; ApportionedValor ; "") ;
IF( MAX('Calendário'[Date]) = StartDate ; ApportionedAmount;"" )
)

In the data model the calendar table is not related to table 4, where there are the columns informed in the example above. The measure is not bringing me the total amount in the sum. Would anyone have a better data model than this? or any suggestions to improve this?

Re: Value spread over a date range

lbendlin — Tue, 22 Sep 2020 22:00:19 GMT

You don't really have dates to work with, you have months. (Is it just me or is the month count off? Shouldn't it be 13?)

Either you create fake date values (first of month) or you do your computations without a calendar table. I would prefer the former approach.

Re: Value spread over a date range

Anonymous — Wed, 23 Sep 2020 12:36:06 GMT

Hi,

Actually, it was 13 months, I rectified the date. I didn't quite understand the first solution. As far as I can already extract the month from the date. In addition, there is another rule where if the date is after the 28th of the month, the apportionment will start the following month.

Re: Value spread over a date range

lbendlin — Wed, 23 Sep 2020 12:57:46 GMT

Then you definitely need a custom Calendar/Dates table. It can be maintained externally (for example an Excel file on a sharepoint) and then consumed as a data source in Power BI.

Re: Value spread over a date range

Anonymous — Wed, 23 Sep 2020 16:22:09 GMT

OK, I can't visualize the solution, would you like to exemplify?