Forum Discussion

kent_n's avatar
kent_n
Frequent Visitor
3 years ago
Solved

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_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 🙂

  • 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

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution 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.

    • kent_n's avatar
      kent_n
      Frequent Visitor

      Worked right away! Thanks alot!


    • kent_n's avatar
      kent_n
      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 😞