Measure w.r.t to dynamic end date
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
Average values of a column within the week before with conditions in the month before
I have a table 'TableSales' with 3 columns 'Date' (in date format), 'Cashier', 'Sale Amount', and another table 'TableDate' with 2 columns 'SelectedDate' (in date format) and 'SelectedDate_text' (date but in text format so that I can have the last date as "The latest day" instead of the date). I use 'TableDate'[SelectedDate_Text] to create a slicer so that users can choose a desired date. I would like to create a measure to calculate the average Sale Amount of Cashiers per day during the latest 7 days until the selected date in the slicer. I can do that with the following query: average = CALCULATE( SUM('TableSales'[Sale Amount])/COUNT('TableSales'[Cashiers]) ,FILTER('TableSales', 'TableSales'[Date <= MAX('TableDate'[SelectedDate]) && 'TableSales'[Date >= MAX('TableDate'[SelectedDate])-6)) However, now I want to filter out all Cashiers with Sale Amount = 0 or blank during the latest 1 month until the selected date because those cashiers are assumed to be broken and shouldn't be taken into the calculation of average. How should I include that into my measure? Thank you.
Slicer Date Range between two columns (filter help)
Hi, This is my Slicer I need to figure out in this date range exist anywhere between these rows for each group#? So I wanna say if _Min_Date_Slicer is between Min(Inc_Start) and Max(Inc_Start) __Max_Date_Slicer is between Min(Inc_End) and Max(Inc_End) FILTER( 'Table', GROUP#=_GRP && _MIN_Date_Slicer >= MIN([INC_START]) && _MIN_Date_Slicer <= MAX([INC_START]) && _MAX_Date_Slicer >= MIN([INC_END]) && _MAX_Date_Slicer <= MAX([INC_END]) ) Can you help with this function? Thanks!
