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!
Dynamic Flag Column That Changes Rows Depending On Today's Date
Hi PowerBi Experts, I am facing an issue where I need to display a table of data depending on which day of the week it is. My dataset sample is as below - Project Name | Transaction Month | Reporting Date | Transaction Week Start | Transaction Week End | Weekly Sales Row 1 Project A | August 2023 | 7 August 2023 | 31 July 2023 | 6 August 2023 | 20 Row 2 Project B | August 2023 | 7 August 2023 | 31 July 2023 | 6 August 2023 | 3 Row 3 Project C | August 2023 | 7 August 2023 | 31 July 2023 | 6 August 2023 | 12 Row 4 Project A | August 2023 | 14 August 2023| 7 August 2023 | 13 August 2023 | 14 Row 5 Project B | August 2023 | 14 August 2023| 7 August 2023 | 13 August 2023 | 5 Row 6 Project C | August 2023 | 14 August 2023| 7 August 2023 | 13 August 2023 | 7 Row 7 Project A | August 2023 | 21 August 2023| 14 August 2023 | 20 August 2023 | 9 Row 8 Project B | August 2023 | 21 August 2023| 14 August 2023 | 20 August 2023 | 0 Row 9 Project C | August 2023 | 21 August 2023| 14 August 2023 | 20 August 2023 | 3 Now, the requirement is that if the user views the report on 14th August 2023 (Monday), the table should only show Rows 1 to 3, to show data within the Transaction Week range. This applies to all subsequent Mondays. From 15th August 2023 (Tuesday) to 20th August (Sunday), the user should only be able to view Rows 4 to 6. This is how the flag should work. What I've tried - I came up with this DAX formula, but it does not work as intended. It should only apply the flag to Rows 4-6, based on today's date (10 Aug), but it applies the flag to Rows 1-3 as well. VAR TodayDate = TODAY() VAR MondayTwoWeeksAgo = TodayDate - WEEKDAY(TodayDate, 2) - 13 VAR MondayOneWeekAgo = TodayDate - WEEKDAY(TodayDate, 2) - 6 RETURN IF( [Transaction Week Start] >= MondayTwoWeeksAgo && [Transaction Week Start] <= MondayOneWeekAgo, "Flag", BLANK() ) I am at a lost, and have been cracking my head for hours now, how can I achieve the aboe - any help will be greatly appreciated! Thanks & Best Regards, LeeWJ
