Compare two overlapping sets of date ranges
Hi - thought I could nail this, but it keeps going wrong. Might be more complicated than I thought. Looking for your ideas. Trying to figure this out using Power Query and Power Pivot / Dax in Excel; Consider this setup; 1) A list of dates that represents rentals of a sort. 2) A second list of dates that represents a rebate period. The condition for rebate is 120 days per rebate period. At the moment rebate periods are for one year, thus 120 days within a year. Starting anywhere in a year (not following calendar year, but initiated by first rental after 01-10-2024. Following this the rebate maximum could be reached inside of a rental period when reaching 120 days. Part of the rental with rebate, part without. Furthermore there could be more than one rental at a given date, but it still only uses one day of rebate. Might be easier with some example data as it looks after preparation in Power Query; 1) List of rentals from 3 customers (id): Id Rents Rental/fiscal Year Rent start Rent end Disp 1 sku 2024 29-04-2024 06-05-2024 18000 1 po 2024 06-05-2024 26-06-2024 90000 1 he 2024 26-06-2024 04-09-2024 110000 1 skn 2024 04-09-2024 08-09-2024 6000 1 skf 2024 09-09-2024 31-12-9998 60000 2 sa 2024 16-09-2022 31-12-9998 400000 2 sa 2023 16-09-2022 31-12-2023 150000 3 ka 2024 13-05-2024 31-12-9998 320000 Disp is the cost in the rental/fiscal year. 2) Rebate periods: Id Rebate scheme Rebate start Rebate end 1 Rebate 120 29-04-2024 31-12-9999 2 Rebate 120 01-10-2024 31-12-9999 2 Rebate 120 01-10-2023 30-09-2024 3 Rebate 120 13-05-2024 31-12-9999 Only one rebate scheme at the moment and always runs for one year after start date. Thus rebate end is not needed, but maybe other rebate schemes will surface later. I believe I need to join these two tables either in Power Query or in the data model in Power Pivot. If joined i would like to be able to get something like this for customer 2; Id Rebate scheme Rebate start Rebate end Rents Rental/fiscal Year Rent start Rent end Disp Year 2023 Year 2024 2 Rebate 120 01-10-2023 30-09-2024 sa 2023 16-09-2022 31-12-2023 150000 92 0 (92 days of 120 spend in 2023) 2 Rebate 120 01-10-2023 30-09-2024 sa 2024 16-09-2022 31-12-9998 400000 0 28 (28 - up to cap - spend in 2024) 2 Rebate 120 01-10-2024 31-12-9999 sa 2023 16-09-2022 31-12-2023 150000 0 0 (obsolete line - no overlap) 2 Rebate 120 01-10-2024 31-12-9999 sa 2024 16-09-2022 31-12-9998 400000 0 28 (same 28 as in line 2!!!) For customer 1 something like this: id Rebate scheme Rebate start Rebate end Rents Rental/fiscal Year Rent start Rent end Disp 2024 2025 1 Rebate 120 29-04-2024 31-12-9999 sku 2024 29-04-2024 06-05-2024 18000 8 0 1 Rebate 120 29-04-2024 31-12-9999 po 2024 06-05-2024 26-06-2024 90000 52 0 1 Rebate 120 29-04-2024 31-12-9999 he 2024 26-06-2024 04-09-2024 110000 62 0 1 Rebate 120 29-04-2024 31-12-9999 skn 2024 04-09-2024 08-09-2024 6000 0 0 1 Rebate 120 29-04-2024 31-12-9999 skf 2024 09-09-2024 31-12-9998 60000 0 0 Customer 1 has spend he/hers 120 days within rental in line 3. No new rebate scheme entered for the period after 28-04-2025. Thank you for reading to the end and I am looking forward to any hints you might be able to give me. Tried a bit with chatgpt, but could not nail it - or my prompting are not clear enough.
Compare data/calculations for a chosen month with same month last year
Hello there TLDR: Based on a chosen month selected with a slicer (custom "Timeline 2.4" slicer) to compare numbers with exact same month last year. So lets say the user selects April 2023, I also want to show data for April 2022, especially the difference in %. I've been searching and searching and tried different solutions without luck. So I have some electricity sensor data for lets say 3 years based upon this calculated measure: Calculated_consumption= (CALCULATE(SUM(Measurements[numericValue]), Measurements[field] = "consumption", Measurements[deviceid] = "X") / 1000). There are timestamp, date, month-year etc columns. I've tried with all columns refering to some kind of "date" or "timestamp"... When the user selects for example April 2023 the dashboards shows electricity consumption for that given month. But I also want a box showing the change from April 2022. So far I've only managed to create tables and graphs that can compare to previous month.. The solutions that didn't work for me: - X = CALCULATE(([Calculated_consumption]), DATEADD(Measurements[MonthYear],-1, YEAR)) - Y = CALCULATE([Calculated_consumption], SAMEPERIODLASTYEAR(Measurements[MonthYear])) - Z = CALCULATE([Calculated_consumption], PARALLELPERIOD(Measurements[MonthYear],-12,MONTH)) Thanks in advance
Compare two contexts to the same fact table
I have a report with two Date tables D1 and D2 and two exact dimensions C1 and C2. The reason is that I need two filter contexts to the fact table. D2 and C2 filter the fact table directly and D1 and C1 through the next dimensions. I chose this approach because the USERELATIONSHIP with just one D and C didn't work as expected. In the report, I have two separate sheets (one showing the context of D1,C1 and the second showing the second ones) and two separate filter pages for both. But now I need to show the data for all dates from D2 (without additional filter context thanks to other dimensions) but also reflect only the filtered C1 dimension. In other words, I need to compare the whole date history with the filtered one but only for relevant attributes from C1. I tried this but it doesn't work: CALCULATE([# Rollout Calls],ALLEXCEPT('ETL_CZ_CICX NLP_RESULT_EXT_ATTRIB','ETL_CZ_CICX NLP_RESULT_EXT_ATTRIB'[DIRECTION],'ETL_CZ_CICX NLP_L2_LOV'[L2_CZ],'Date_AllCalls'[Date]),FILTER('ETDW PDW_CELLS_AllCalls',[# Rollout Calls] >0 )) Thanks for any help!
Dax
Hi everybody. I recived weekly data in excel, all data have a column (date) and comes for the day that i recived data in (Thursday). So I created dashboard contains all weekly data and I can compare with previous week. for the comparison I used these formulas ( weekNum = WEEKNUM('Date'[Date]) WeekEndDate = 'Date'[Date]+7-1*WEEKDAY('Date'Date],1) WeekStartDate = 'Data'[Data]+-1*WEEKDAY('Date'[Date],1)+5 WeekRang = RANKX(ALL('Date'),'Date'[WeekStartDate],,ASC,Dense)). Also, I have filter represents all dates so I can select any date and compare it with previous week. Today, I recived data by month and I want adjust my formula to work with both. *Note: I can not sum weeks to get data for months. Thanks.
Comparing multi selected averages with total average on a line graph
Hello All, Another newbie to Power BI and struggling with a problem.... I am trying to compare NHS services against each other and against a national average (calculated via a measure, 'National Averages'), by a particular metric. However the moment a second service within my slicer, the two are aggregated into a single line. However the moment a second service within my slicer, the two are aggregated into a single line, alongside the national average, when I'd like a third line instead. I have tried adding the service name to the legend however then I cannot add my 'National Averages' to the Y-axis. I have also tried and failed to solve the problem using the SWITCH function, based on this post and solution, creating separate measures each service but again, once I make a second selection the result aggregates the results into a single line, including the national average, when again, I want 3 lines or more if I choose additional services. https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-get-multiple-results-from-SWITCH-function/m-p/1380904 National Averages = CALCULATE(AVERAGE('Ambulance KPI data'[Value]), ALL('Org Info'[Org Name])) East Midlands Ambulance Service NHS Trust = SWITCH( SELECTEDVALUE('Org Info'[Org Name]), "East Midlands Ambulance Service NHS Trust", AVERAGE('Ambulance KPI data'[Value]), BLANK() ) East of England Ambulance Service NHS Trust = SWITCH( SELECTEDVALUE('Org Info'[Org Name]), "East of England Ambulance Service NHS Trust", AVERAGE('Ambulance KPI data'[Value]), BLANK() ) Can anyone advise where my approaches are going wrong/an alternative approach? Hope this clear! If not please do not hesitate to ask!
Comparison of column value to average of column total
Hello Community, Excuse the fact that I might be posting this with a possible solution floating around the community, I've tried to use different solutions found around the community but none seem to fully cover the requirement I have, either that or I may not be approaching this issue correctly. In any case, I have a table that represents every project that is ongoing or completed, alongside that is a column that represents the number of active days per project (in the project's lifecycle is a total number of active days for that project as well as a number of days where the project was idle/on-hold). From this column I'd like to calculate the total average based on selected values filtered by slicers and compare whether the project's number of active days is greater or less than the generated average. Below is an example of what I'm trying to achieve, the representation might be lackluster but: Input: Output would be: If filtered for market 'A' In this case since the selected filter is for Market 'A' the avg is computed based on the values extracted for this filter and generates each project's status based on the comparison between that average and the project's # of Active Days, I would like to be able to implement this either based on a Market slicer selection or Customer slicer selection. I've pretty much managed to nail the logic for the status and comparison and at one point event the average but strictly on a market basis but can't seem to find the right way to compute the average to adjust based on selected market or customer slicer. Any help or suggested solutions are greatly appreciated. Best, Sam
