How to create periods with date ranges

[edlng]

Hi!
I'm just starting PowerBI and need some help on a formula. I have five columns: site, date, energy, consumption, average unit price and cost.

I would like to create 6 different periods with ranges of dates, like May 2021 to July 2022 for instance. 

How can I do this to have one row per site and type of energy, with consumption and cost totals; and average unit price for each period?

For example:
London | May 2021-July 2022 | Electricity | total consumption over the period | average unit price over the period | total cost over the period
London | May 2021-July 2022 | Gas | total consumption otp | average unit price otp | total cost otp

Mainly, is it possible to do it with a formula?

I followed courses but didn't find the answer; that's why I wanted to ask here.

Thank you!

[BeaBF · Accepted solution]

@edlng Hi! Here the steps:

1. Create a table with date ranges:

Periods =
DATATABLE(
"Period", STRING,
"Start Date", DATE,
"End Date", DATE,
{
{"May 2021 - July 2022", DATE(2021,5,1), DATE(2022,7,31)},
{"Aug 2022 - Dec 2022", DATE(2022,8,1), DATE(2022,12,31)},
{"Jan 2023 - Jun 2023", DATE(2023,1,1), DATE(2023,6,30)}
-- Add more periods here
}
)

2. Now, you need to create a relationship:
Go to Model View and connect the Periods[Start Date] and Periods[End Date] to your main table's Date column.

3. Calculates these measures:

Total Consumption =
CALCULATE(
SUM('YourTable'[Consumption]),
TREATAS(
FILTER(Periods,
'YourTable'[Date] >= Periods[Start Date] &&
'YourTable'[Date] <= Periods[End Date]
),
'YourTable'[Date]
)
)

Avg Unit Price =
DIVIDE(
SUMX('YourTable', 'YourTable'[Consumption] * 'YourTable'[Average Unit Price]),
SUM('YourTable'[Consumption])
)

Total Cost =
CALCULATE(
SUM('YourTable'[Cost]),
TREATAS(
FILTER(Periods,
'YourTable'[Date] >= Periods[Start Date] &&
'YourTable'[Date] <= Periods[End Date]
),
'YourTable'[Date]
)
)

4. Add a Matrix Visual.

Place:
Rows: Site, Period, Energy Type
Columns: (None, since you want it by row)
Values: Total Consumption, Avg Unit Price, Total Cost

BBF

[BeaBF · Accepted solution]

@edlng My code is to create a Calculated Table in DAX. You're in Power Query trying to calculate a custom column. Exit the power query, and click on Modeling > new Table

BBF

[v-bmanikante · Accepted solution]

Hi @edlng,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
B Manikanteswara Reddy

[edlng]

Thank you for your answer @BeaBF!
An error message popped up when I tried the first formula:

Do you know what went wrong?

[BeaBF · Accepted solution]

@edlng My code is to create a Calculated Table in DAX. You're in Power Query trying to calculate a custom column. Exit the power query, and click on Modeling > new Table

BBF

[BeaBF · Accepted solution]

@edlng Hi! Here the steps:

1. Create a table with date ranges:

Periods =
DATATABLE(
"Period", STRING,
"Start Date", DATE,
"End Date", DATE,
{
{"May 2021 - July 2022", DATE(2021,5,1), DATE(2022,7,31)},
{"Aug 2022 - Dec 2022", DATE(2022,8,1), DATE(2022,12,31)},
{"Jan 2023 - Jun 2023", DATE(2023,1,1), DATE(2023,6,30)}
-- Add more periods here
}
)

2. Now, you need to create a relationship:
Go to Model View and connect the Periods[Start Date] and Periods[End Date] to your main table's Date column.

3. Calculates these measures:

Total Consumption =
CALCULATE(
SUM('YourTable'[Consumption]),
TREATAS(
FILTER(Periods,
'YourTable'[Date] >= Periods[Start Date] &&
'YourTable'[Date] <= Periods[End Date]
),
'YourTable'[Date]
)
)

Avg Unit Price =
DIVIDE(
SUMX('YourTable', 'YourTable'[Consumption] * 'YourTable'[Average Unit Price]),
SUM('YourTable'[Consumption])
)

Total Cost =
CALCULATE(
SUM('YourTable'[Cost]),
TREATAS(
FILTER(Periods,
'YourTable'[Date] >= Periods[Start Date] &&
'YourTable'[Date] <= Periods[End Date]
),
'YourTable'[Date]
)
)

4. Add a Matrix Visual.

Place:
Rows: Site, Period, Energy Type
Columns: (None, since you want it by row)
Values: Total Consumption, Avg Unit Price, Total Cost

BBF