Measure w.r.t to dynamic end date

[abhiram342]

Hi all - I created a measure that serves two purposes: by default, it displays the latest sales, and when the user applies a date filter, it shows the average sales. However, currently, when new dates are added, users need to manually select the date to ensure the average sales are correctly reflected. Could you suggest alternative solutions? I'm looking to address this without creating multiple measures, aiming to solve it with a single measure.

Model:

Data and Measures:

Measure:

Total Sales Amt =

VAR LatestDate =  MAX ( 'Is Latest'[Latest Sales Date] )

VAR LatestData = CALCULATE(SUM('FactSales'[SalesAmt]), 'DimDate'[Date] = LatestDate)

RETURN IF (ISCROSSFILTERED('DimDate'[Date]),SUM('FactSales'[SalesAmt]), LatestData)

Average Sales Amt =

VAR TotalSales = 'FactSales'[Total Sales Amt]

VAR NoOfDays = IF (ISCROSSFILTERED('DimDate'[Date]), SUM('DimDate'[No Of Days Sales]), 1)

RETURN DIVIDE(TotalSales,NoOfDays,0)

 

I'm using the Average Sales Amount measure for both the Latest Sales visual and the Average Sales Amount visual. For the Latest Sales Amount, I've implemented an IsLatest flag.

Working Scenario: When the user selects a date in the slicer, everything works as expected.

Not Working Scenario: When a new date is added (e.g., 8/17/2024), it automatically appears in the date slicer, but the Average Sales Amount doesn't update because of the cross-filter logic in the measure. Users have to manually select the new date to display the correct value.

Is there a way to automatically update the Average Sales Amount without requiring the user to select the date in the slicer?

Latest Sales:

Enabled Edit Ineractions: ( For Latest Sales, Slicer will not interact)

Average Sales : Same Measure No Filter

 Selecting Date Slicer:

Average Sales Amount updates Correctly

Thanks,

Abhiram

[lbendlin]

When a new date is added 

How is that date added? Is it a Direct Query data source?

[abhiram342]

Hi @lbendlin  - Fact is daily grain , when data refreshes new date gets added to fact . It's import model.  

thanks,

Abhiram

[lbendlin]

There is nothing you can do. By design the user must refresh the page to see the semantic model changes. APR is only available for Direct Query, I think.