cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## month range combines with time range

Hi

Im an absolute novice and tried google with no luck.

Completely failed my first try to explain my issue (thanx @daXtreme :), hopefully this might explain what I want to achieve.

The following measure is working just fine, but only between april 1st and october 31st.

From november 1st to march 31st the filtered value

fixed_prices[Pristabell]="Profit_high" for all the hours between 06-22 (06AM-10PM)
Hours between 22-06 (10PM-06AM) are always "Profit_low" year around.

``````Subvention & Grid Compensation =
(CALCULATE(
SUMX(FILTER(
fixed_prices,fixed_prices[Pristabell]="Elsubvention"||fixed_prices[Pristabell]="Profit_low"),fixed_prices[öre/KwH (ink moms)])) / 100)
* SUM(Energy_sales_price[Production])``````

Energy_sales_price

fixed_prices

hour_table

date_table

1 ACCEPTED SOLUTION
Solution Sage
``````Subvention & Grid Compensation =
// To get what you want you have to
// iterate over Date and Timme and
// take action accordingly.
// The dimensions should be connected
// by 1->>-* to the fact table. fixed_prices
// is a stand-alone table without any connections.
//
// Then you can write something like:
var SwitchMonthNumbers =
{
1,  -- jan
2,  -- feb
3,  -- mar
11, -- nov
12  -- dec
}
var Output =
sumx(
summarize(
Energy_sales_price,
date_table[Date],
// Make sure you've got MonthNumbers
// in date_table.
date_table[MonthNumber],
hour_table[Timme],
hour_table[High_low_hour]
),
var CurrentMonthNumber = date_table[MonthNumber]
var TotalProduction =
calculate(
SUM( Energy_sales_price[Production] )
)
var HighLowProfit =
if( hour_table[High_low_hour] = "High",
"Profit_high",
"Profit_low"
)
var FixedPriceTableFilter =
{
"Elsubvention",
if(
CurrentMonthNumber in SwitchMonthNumbers,
HighLowProfit,
"Profit_low"
)
}
var FixedPrice =
SUMX(
FILTER(
fixed_prices,
fixed_prices[Pristabell] in FixedPriceTableFilter
),
fixed_prices[öre/KwH (ink moms)]
)
* TotalProduction / 100
return
FixedPrice
)
return
Output``````

Something like this. I did not do any optimization on this. Wrote it without any real data and model. Might work... might not. Try to adjust it to your model.

4 REPLIES 4
Solution Sage
``````Subvention & Grid Compensation =
// To get what you want you have to
// iterate over Date and Timme and
// take action accordingly.
// The dimensions should be connected
// by 1->>-* to the fact table. fixed_prices
// is a stand-alone table without any connections.
//
// Then you can write something like:
var SwitchMonthNumbers =
{
1,  -- jan
2,  -- feb
3,  -- mar
11, -- nov
12  -- dec
}
var Output =
sumx(
summarize(
Energy_sales_price,
date_table[Date],
// Make sure you've got MonthNumbers
// in date_table.
date_table[MonthNumber],
hour_table[Timme],
hour_table[High_low_hour]
),
var CurrentMonthNumber = date_table[MonthNumber]
var TotalProduction =
calculate(
SUM( Energy_sales_price[Production] )
)
var HighLowProfit =
if( hour_table[High_low_hour] = "High",
"Profit_high",
"Profit_low"
)
var FixedPriceTableFilter =
{
"Elsubvention",
if(
CurrentMonthNumber in SwitchMonthNumbers,
HighLowProfit,
"Profit_low"
)
}
var FixedPrice =
SUMX(
FILTER(
fixed_prices,
fixed_prices[Pristabell] in FixedPriceTableFilter
),
fixed_prices[öre/KwH (ink moms)]
)
* TotalProduction / 100
return
FixedPrice
)
return
Output``````

Something like this. I did not do any optimization on this. Wrote it without any real data and model. Might work... might not. Try to adjust it to your model.

Frequent Visitor

Sorry, eent through the whole year and double checked some vaulues.

I forgot that the conditions only apply to monday-friday! Saturday and sundays are always Low.

I´ve added a Column in my date_table named Day of week with monday = 0 and sunday = 6

I can´t figure out how to add this 😞

Frequent Visitor

Worked right away! Thanks alot!

Solution Sage

Completely unclear. Please get familiar with this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors