month range combines with time range

[kent_n]

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_low" should instead be 

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

Thanks in advance 🙂

[daXtreme · Accepted solution]

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.

[kent_n]

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 😞

[kent_n]

Worked right away! Thanks alot!

[daXtreme]

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