topic Re: month range combines with time range in DAX Commands and Tips

month range combines with time range

kent_n — Sat, 05 Nov 2022 14:42:25 GMT

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 🙂

Re: month range combines with time range

daXtreme — Sat, 05 Nov 2022 12:44:08 GMT

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

Re: month range combines with time range

daXtreme — Sat, 05 Nov 2022 19:16:38 GMT

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.

Re: month range combines with time range

kent_n — Sun, 06 Nov 2022 12:24:11 GMT

Worked right away! Thanks alot!

Re: month range combines with time range

kent_n — Sun, 06 Nov 2022 13:15:13 GMT

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 😞