Forum Discussion
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
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 OutputSomething 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
Solution Sage
Completely unclear. Please get familiar with this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community
- daXtreme
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 OutputSomething 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_nFrequent Visitor
Worked right away! Thanks alot!
- kent_nFrequent 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 😞