Removing a slicer interaction while still using the slicer value
In my report, a user will select a date using a date between slicer and we're only using the minimum date selected. This date can be any day of the week. I want to show the sales numbers for the entirety of that week. For example, if a user selects a Wednesday, I want to show the values for Sunday through Saturday of that week. I have tried several remedies, but it seems like I either show the days leading up to the selected day or the days after the selected day-- I have not been able to see the entire week at once. One attempt of a calculation was CALCULATE(sum(*redacted sales table info*),ALL('vw_D_Date'[date_value]),FILTER(*redacted sales table*,[MinimumSelectedDate])) where MinimumSelectedDate = FIRSTDATE('vw_D_Date'[date_value]) Any suggestions on how to solve this?
Calculate variances between various columns depending on the value date vs current date
Hello, I have two tables, one is a Target Amounts table which has a Target Amount and an Outlook Amount. The other one is an Actuals Amounts Table, that has the Actual Amounts in it (Both shown below). These fact values have corresponding 1-many relationships with some dimension tables, so all data can line up as needed. I need to write a measure that returns a value based on the following logic: If the amounts are within 6 months of the current date then: IF: Target Amount > Actuals Amount, return Target Amount - Outlook Amount Else, Return Actuals Amount - Outlook Amount Else, return Actuals Amount - Outlook Amount Intended result: So, if the current date is July 2023, then the measure would return the results in the far right column in the picture below. This is the formula I tried. The results are significantly larger than what was intended (hundreds of Billions in variances when it should be 10s of millions in variances). Any ideas on what I need to tweak? OutlookVarianceCalculation = //Create variables VAR CurrentDate = TODAY() //Current date variable VAR SixMonthsAgo = EDATE(CurrentDate, -6) //6 months prior to current date VAR TargetAmount = SUM(Cumulative_Targets[Current Month Net Target]) //Target amount VAR ActualsAmount = SUM(CMS_Gross_Actuals[Net_Amount]) //Actuals Amount VAR OutlookAmount = SUM(Cumulative_Targets[Outlook]) //Outlook Amount RETURN SUMX( FILTER( //Filter table on dates within the past 6 months ALL('Date Dim'), 'Date Dim'[First_Of_Month] >=SixMonthsAgo && 'Date Dim'[First_Of_Month] <= CurrentDate ), IF( //Return either target vs outlook variance or actuals vs outlook variance based on the logic below TargetAmount > ActualsAmount, //logic TargetAmount - OutlookAmount, // return if true ActualsAmount - OutlookAmount // return if false ) ) + SUMX( //Also sum variances past 6 months using the actuals - outlook logic. FILTER( ALL('Date Dim'), 'Date Dim'[First_Of_Month] < SixMonthsAgo || 'Date Dim'[First_Of_Month] > CurrentDate ), ActualsAmount - OutlookAmount ) Tables: Actuals Table Service Month Posting Date Region Sub Region Reporting Category Actuals Amount 1/1/2023 0:00 1/1/2023 0:00 Region 1 Cat 1 $503,972 2/1/2023 0:00 2/1/2023 0:00 Region 1 Cat 1 $1,099,065 1/1/2023 0:00 3/1/2023 0:00 Region 1 Cat 2 $86,573 2/1/2023 0:00 4/1/2023 0:00 Region 1 Cat 2 $10,277 2/1/2023 0:00 5/1/2023 0:00 Region 1 Cat 2 ($42,746) Target Table Service Date Region Sub Region Reporting Category Target Amount Outlook Amount 1/1/2023 0:00 Region 1 Cat 1 $600,000 550,000.00 2/1/2023 0:00 Region 1 Cat 1 $1,000,000 900,000.00 1/1/2023 0:00 Region 1 Cat 2 $80,000 90,000.00 2/1/2023 0:00 Region 1 Cat 2 $10,000 10,000.00 2/1/2023 0:00 Region 1 Cat 2 ($45,000) (50,000.00) Table Relationships: Each dimension has a dimension table that has a 1-Many relationship with the fact table. The dates used in my dax formula come form my date dimension table.
DAX for difference between each date and previous date per occurrence
Hi, Please help with DAX to accomplish the following? For each vehicle many jobs are performed at various date intervals. Each time a job is performed, the date (JOB_OPEN_DATE) is captured. Below in the table you can see the JOB_OPEN_DATEs when different jobs in the JOB column were performed at various dates for one selected vehicle. (There are of course many more vehicles.) Question: I don't want to display the actual date when each job was performed as you can see in the below table, but rather the difference between each date and the previous date for each job - in number of days. This is to see clearly the interval (in days) at which the jobs were performed. This means e.g. for the first Job 06-02-005, 2021 June should display (2021-06-23 minus 2020-06-17) = 371 (days). In the same way, 2022 July should display (2022-07-05 minus 2021-06-23) = 377 (days). The ideal solution should like like the below showing number of days: All the data is captured in the same WO WITH JOB DETAIL table. Here sample data from this table: Sample dataset . The data captured in Power BI is here. Not sure if I should add a Date/Calendar table. This Power Bi file contains a date table. You will note the interval between the various jobs differs. One would typically filter on a specific vehicle to monitor service intervals for its many jobs. With thanks, Water
