Forum Discussion

WhaleWatcher222's avatar
WhaleWatcher222
Icon for Helper II rankHelper II
1 year ago
Solved

Measure showing future actuals based on Slicer | Filter selection

Hi Expert

 

I cannot get the folllowing measure to Show actuals based on the filter selection. 

 

New Actuals = 
IF(HASONEVALUE('Date'[Month]) && HASONEVALUE('Date'[Year]),
    CALCULATE(
    SUM('Financial Datasets'[Amount]),
    'Financial Datasets'[Dataset] = "Actual",
    REMOVEFILTERS('Financial Datasets'[Dataset]),
    REMOVEFILTERS('Financial Datasets'[Budget Name])

), BLANK())

I have tried inscope too.

 

So if i select Nov 24 in my month Year filter from my Date table only show actuals upto Nov 24. and not for Dec24 jan 25 and so on. 

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

3 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    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.

    • WhaleWatcher222's avatar
      WhaleWatcher222
      Icon for Helper II rankHelper II
      1 year ago

      Hi 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's avatar
        rohit1991
        Icon for Super User rankSuper User
        1 year ago

        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
)