Need to SUM values in a date range without a date table

[Anonymous]

I've inherited a report made by someone else with a budget column in it that is calculated by looking at the dates from a date slicer. This isn't a date table but is a measure to get a date value. 

I need this budget measure to SUM the values that are selected in the date range from the 'date selector'. Currently it only gives the MAX value in relation to the date selector. 

The measure is as follows: 

MonthSelected NewBudget =

VAR MAXMonth = MONTH(MAX(Table[Date Selector]))

RETURN

SUMX(

FILTER(

VALUES(BudgetNew[Month Num]),

BudgetNew[Month Num] = MAXMonth

),

CALCULATE(SUM(BudgetNew[Budget]))

)

Any help greatly appreciated, thankyou!

[123abc]

It looks like the measure you provided is designed to calculate the sum of the budget for the month selected in the 'date selector'. If you want it to sum values within a date range rather than just the selected month, you can modify the measure accordingly.

Assuming your 'date selector' provides a range, you can use the following approach:

BudgetInRange =
VAR StartDate = MIN(Table[Date Selector])
VAR EndDate = MAX(Table[Date Selector])
RETURN
CALCULATE(
SUM(BudgetNew[Budget]),
BudgetNew[Date] >= StartDate && BudgetNew[Date] <= EndDate
)

In this modified measure:

1. I've replaced the use of MAX(Table[Date Selector]) with MIN(Table[Date Selector]) for the start date and MAX(Table[Date Selector]) for the end date.

2. I've used the CALCULATE function to filter the rows based on whether the date is within the specified range.

This measure should now give you the sum of the budget for the selected date range. Adjust the column and table names as needed to fit your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

I believe that this has given me my desired result so thankyou! 

I have got some strange behaviour where not all sites results are displaying, yet the total is the same as the old measure. Do you know what could be causing this? 

[123abc]

It's great to hear that the modified measure has provided the desired result!

Regarding the issue where not all sites' results are displaying, there could be several reasons for this behavior. Here are a few things you can check:

1. Filter context: Ensure that there are no additional filters applied to your report or visual that might be restricting the data for certain sites. Check if there are any slicers, cross-filtering, or other filters that could be affecting the results.

2. Relationships: Verify that the relationships between the tables in your data model are correctly defined. Make sure that the relationships are based on the appropriate columns and that they are in the correct direction.

3. Data completeness: Ensure that the 'BudgetNew' table contains data for all the sites you expect to see in the report. Check for missing or incomplete data that might be causing certain sites to be excluded.

4. Date format compatibility: Confirm that the date format used in the 'Date Selector' matches the date format in the 'BudgetNew' table. In Power BI, date formats need to match for proper filtering to occur.

If the issue persists, you may want to provide more details about your data model, relationships, and any specific filters or slicers you are using. Additionally, examining the data for the sites that are not displaying correctly might help identify any patterns or issues in the data.

If you can provide more details or share a screenshot of your data model, relationships, and any relevant filters, I'd be happy to assist you further.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

I've found the solution to getting all of the results to display. However, the SUM is not working correctly. It isn't summing the values from the budget together. 

The 5th from the bottom site has a budget value of 1 every month, the filter range is 2 months so this value should be 2.  

[123abc]

If the SUM is not working correctly and not aggregating the budget values as expected, there might be an issue with the context in which the calculation is being performed. It's possible that the current filter context is not being overridden as intended.

To address this, you can try using the ALL function within the CALCULATE statement to remove all filters from the BudgetNew table, except for the date range filter. Here's an updated version of your measure:

BudgetInRange =
VAR StartDate = MIN(Table[Date Selector])
VAR EndDate = MAX(Table[Date Selector])
RETURN
CALCULATE(
SUM(BudgetNew[Budget]),
ALL(BudgetNew), -- Remove all filters from BudgetNew table
BudgetNew[Date] >= StartDate && BudgetNew[Date] <= EndDate
)

Adding the ALL(BudgetNew) part inside the CALCULATE function ensures that all other filters on the BudgetNew table are removed, except for the date range filter specified in the second argument. This should help in getting the correct sum of budget values within the selected date range.

Make sure to replace "Date" in the code with the actual column name in your BudgetNew table that contains the date values. Adjust the column names accordingly based on your data model.

If the issue persists, consider checking if there are any other filters, slicers, or context in your report that might be affecting the results. Additionally, reviewing the data for the specific site and date range in question could help identify any anomalies.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.