Forum Discussion
Helper IIMeasure showing future actuals based on Slicer | Filter selection
Hi WhaleWatcher222 ,
The issue seems to be with how filters are being handled in your measure. Although you're using REMOVEFILTERS to clear filters on the dataset and budget name, you're not explicitly filtering the date range to only include dates up to the selected month. Instead of relying on HASONEVALUE, consider using a more dynamic approach where you determine the latest selected date and then filter the data accordingly. You can use a measure like:New Actuals = VAR MaxSelectedDate = MAX('Date'[Date]) RETURN CALCULATE( SUM('Financial Datasets'[Amount]), 'Financial Datasets'[Dataset] = "Actual", 'Date'[Date] <= MaxSelectedDate )This way, it will only sum actuals up to the selected month, respecting the context of your slicer or filter.
Super UserHi WhaleWatcher222 ,
The issue seems to be with how filters are being handled in your measure. Although you're using REMOVEFILTERS to clear filters on the dataset and budget name, you're not explicitly filtering the date range to only include dates up to the selected month. Instead of relying on HASONEVALUE, consider using a more dynamic approach where you determine the latest selected date and then filter the data accordingly. You can use a measure like:
New Actuals =
VAR MaxSelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
SUM('Financial Datasets'[Amount]),
'Financial Datasets'[Dataset] = "Actual",
'Date'[Date] <= MaxSelectedDate
)
This way, it will only sum actuals up to the selected month, respecting the context of your slicer or filter.
WhaleWatcher222Hi Rohit slight issue - the following table is connect to the Date which connect to the FACT table (date v Date) but
_MonthYearTable = DISTINCT( SELECTCOLUMNS( FILTER( 'Financial Datasets', 'Financial Datasets'[Date] <= TODAY() -- Filter out future dates ), "MonthYear", FORMAT('Financial Datasets'[Date], "mmm yy"), "Year", YEAR('Financial Datasets'[Date]), "_MonthYearSort", FORMAT('Financial Datasets'[Date], "YYYYmm"), "Month", FORMAT('Financial Datasets'[Date], "Mmm") ) )Connect to Date based on Month and Year column.... The month year from the about table is driving the filter
- rohit1991
Hi WhaleWatcher222 ,
Since your slicer and filtering are coming from the Month/Year table (not the actual Date table), Power BI can only filter your data at the whole month level. So when you pick "Nov 2024," you’ll always get all actuals for that month there’s no way for the slicer to know you want to cut off at, say, Nov 24 instead of Nov 30.To filter up to a specific date (like Nov 24), you’d need to use a slicer on the actual Date field from your Date table. The measure I posted before with MaxSelectedDate = MAX('Date'[Date]) works perfectly if you select by date, but can’t break down by day if you’re only slicing by month and year.
If you want to stick with just Month/Year filters: You’ll always see the whole month’s data. That’s just how the model works. However, if you want something a little smarter (for example, if you select the current month, only sum up to today, but for past months sum the whole month), you can use a DAX measure like this:
New Actuals = VAR SelectedYear = SELECTEDVALUE(_MonthYearTable[Year]) VAR SelectedMonth = SELECTEDVALUE(_MonthYearTable[Month]) VAR LastDate = IF( SelectedYear = YEAR(TODAY()) && SelectedMonth = MONTH(TODAY()), TODAY(), EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0) ) RETURN CALCULATE( SUM('Financial Datasets'[Amount]), 'Financial Datasets'[Dataset] = "Actual", 'Date'[Date] <= LastDate )