Calculate yearly units based on date range

[wfo]

Dear Community,

I have a database which shows me consumed units based on a begin and end date. The table 'Consumption' looks like this:

ID	Begin	End	ConsumedUnits
1	09/10/2018	09/14/2019	1458
2	09/15/2019	10/02/2020	2435
3	10/03/2020	10/01/2021	2100
4	10/02/2021	09/26/2022	2801

I would like to calculate and visualize the Consumed Units for each year (2018, 2019, 2020). The consumed units should for each year should be calculated with the average per day beween start and end date.

For 2018: timespan = 370 days; 3.94 consumed units per day in average; timespan until 12/31/2018 = 113 days; result is 445.28 consumed units in 2018

I tried with a DAX table but my approach was not that successful.

Thanks in advance for any assistance,

Wolfgang

[mohdasaad94 · Accepted solution]

You can use DAX to calculate the yearly consumed units based on the date range in the 'Consumption' table. Here's how you can do it:

1. Create a new calculated column in the 'Consumption' table that calculates the number of days between the begin and end dates:

Days = DATEDIFF(Consumption[Begin], Consumption[End], DAY) + 1

The "+ 1" is added to include the end date in the calculation.

1. Create another calculated column that calculates the daily average consumed units:

AvgUnitsPerDay = Consumption[ConsumedUnits] / Consumption[Days]

1. Create a new table that contains the years you want to calculate the consumed units for:

Years = DISTINCT(YEAR(Consumption[Begin]))

This will give you a table with one column called "Years" that contains the distinct years from the 'Begin' column in the 'Consumption' table.

1. Create a calculated column in the 'Years' table that calculates the consumed units for each year:

ConsumedUnits = VAR SelectedYear = Years[Years] RETURN SUMX( FILTER(Consumption, YEAR(Consumption[Begin]) = SelectedYear), IF( YEAR(Consumption[Begin]) = SelectedYear, IF( YEAR(Consumption[End]) = SelectedYear, Consumption[ConsumedUnits], Consumption[Days] * Consumption[AvgUnitsPerDay] ), IF( YEAR(Consumption[End]) = SelectedYear, (DATEDIFF(DATE(YEAR(Consumption[End]), 1, 1), Consumption[End], DAY) + 1) * Consumption[AvgUnitsPerDay], (DATEDIFF(DATE(YEAR(Consumption[Begin]), 12, 31), Consumption[Begin], DAY) + 1) * Consumption[AvgUnitsPerDay] ) ) )

This formula uses the SelectedYear variable to filter the 'Consumption' table to include only the records that fall within the selected year. It then uses an IF statement to calculate the consumed units for each record based on whether the begin and end dates fall within the selected year or not. If they do, it simply uses the consumed units value. If not, it calculates the consumed units by multiplying the number of days in the selected year by the daily average consumed units.

1. Create a visual that uses the 'Years' table and the 'ConsumedUnits' column to display the yearly consumed units.

This should give you the yearly consumed units based on the date range in the 'Consumption' table.