Help with sales measure between two dates for custom calendar
Hi, I know this is a common ask but I'm stuck on this Dax measure. Basically, I need to calculate the sales variance betwen two dates in my date table for any slice/dimension my user needs. I'm used to using the dateadd or sameperiodlastyear functions for this, but I'm stuck since I'm using custom dates for our new fiscal year calendar. My sales table comes in weekly with a week start column, and a list of stores and products For my date table, I added a "this year" and 'last year" column for the comparison dates. Corpweekstart is connected to my date in this table. I tried the below DAX for YOY sales which works perfeclt in a weekly view. But it appears blank when i look at it in total or as years. From what I've read, it's because of SELECTEDVALUE() and I think i need to use HASONEVALUE() or ISINSCOPE() instead? But I'm not sure if that's correct or what field to use it with? VAR ty = SUM(Sales_Agg[sales]) VAR PrevWeekStart = SELECTEDVALUE ( 'adhoc_ace_date'[ly_week_start] ) VAR ly = IF ( NOT ISBLANK ( PrevWeekStart ), CALCULATE ( SUM(Sales_Agg[sales]), TREATAS ( { PrevWeekStart }, 'adhoc_ace_date'[ace_date] ) ), BLANK () ) RETURN IF (ISBLANK( ly ), BLANK (), (ty - ly)) It works fine when I look at it weekly, but not yearly (or any other dimension).
Need Help with Dax
Hi Power BI Experts, I Need Help with Dax Calculation I Have Table in Power Bi Sales_Data This Table has ColumnsCon ProjectName,| Contract Value | AwardDate | CRV_Q1 |CRV_Q1_Date | CRV_Q2 | CRV_Q2_Date | CRV_Q3 |CRV_Q3_Date | CRV_Q4 | CRV_Q4_Date ProjectA | 50,00,000 | 15th Aug 2025 | 5800000 | 15th Feb 2026 | 57500000 | 16th June 2026 | 6500000 | 30th Sept 2025 | 5500000 | 30th Dec 2025 this is My Project and CVR means (Contract Revised Value) Now i want To Present This commulative in Bar Chart I have Dim Table as Dim_Calender which has 1 to Many Relationship with AwardDate I Want to Populate my Values Like this Since Our Q1 and Q2 is for 2026 and Q3 & Q4 for 2025 I have slicer in Report for Year if i click on Slicer For 2025 It Shows Like This for 2026 I am Not able to get a Proper Way to Populate My contract Value based on this
I want to retain current month's output to use as a value for all succeeding future months.
I've been stuck with this problem for three days already. I've tested multiple date and time intelligence functions to store the output of today's month but all have failed. What I want to happen is that regardless of what the value of the Numerator and Denominator is in the succeeding months, the output to be shown in the succeeding months should be the same as the output given on today's month. For example, since the month today is May 2025, I want the output of Numerator/Denominator in the succeeding months to be the same. Something like the sample shown in the image. Summary of the premise: Today is May For the month of May, and previous, I want Output = Numerator/Denominator. For any month after May I want Output = 67.16% General case: If Date Period is equal to or prior to Current Period, Output = Numerator/Denominator. Else; If Date Period is after Current Period, Output = Numerator of Current Period/Denominator of Current Date PeriodCalculating Active Employees from Most Recent Record
Hello, I am trying to analyze workforce metrics over time. I have a fact table that has a record for each time an employee file has changed. It has at least one record for every employee. This table includes unique employee ID, Start date, division, Term Date, Effective Date (when the record was created) and a calculated column of Record Order- which numbers the rows for each employee, with 1 being the first record, 2 being the second and so on. I also have dimension tables for Date, Org Structure (Division/Section) Demographics I need to count active employees at a point in time where Start date <= SelectedDate, TermDate is blank, or > SelectedDate. The issue I am running into is; if an employee has been active in two divisions at different times during the selected period, they are Distinct Counted in each division. This means that the overall organization numbers are accurate, BUT they will count as an employee in each division when that slicer is applied. I need to be able to use just the most recent record for each unique employee- as that most recent record will show their current location. Example Data: PS ID start date Division Term Date RecordOrder Eff Date 1 1/1/2020 A null 1 5/18/2020 1 1/1/2020 A null 2 5/19/2020 1 1/1/2020 B null 3 7/11/2020 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 1 1/2/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 A null 1 1/1/2020 3 1/1/2020 B null 2 10/3/2020 In the above data set, using a date slicer on say 10/4/2020, I would want to return a distinct count (3) active employees, 2 in division B. 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 B null 2 10/3/2020 The first approach I took was to use an 'events in progress' methodology 1. Measure to count all employees based on Start Date 2. Measure to distinct count all active employees at point in time where start date<= selected date, and term date is blank or after Selected date this returns accurate counts for the whole organization- but when appling division slicers, it counts employees as a member of every division they had been active in at any point. So, if someone was in A and later B- they would show up when slicing by A or B. I need to return only the most recent record. The next approach I tried was to identify the MaxRecordOrder of each ID, and filter the active employees measure where RecordOrder=MaxRecord Order: MaxRecordOrder = CALCULATE( MAX(Employee_Fact[RecordOrder]), ALLEXCEPT(Employee_Fact, Employee_Fact[PS ID], 'DateTable') ) Active Employees= VAR EndDatePerVisual = MAX('DateTable'[Date]) VAR RESULT = CALCULATE( DISTINCTCOUNT(Employee_Fact[PS ID]), REMOVEFILTERS('DateTable'), FILTER( Employee_Fact, Employee_Fact[Start Date] <= EndDatePerVisual && ( Employee_Fact[Term Date] > EndDatePerVisual || ISBLANK(Employee_Fact[Term Date]) ) && Employee_Fact[RecordOrder] = [MaxRecordOrder] ) ) RETURN RESULT This approach seems to correctly identify the max RecordOrder for each ID, responsive to the date slicer- but still counts rows where the MaxRecord order <> RecordOrder- so employees are still showing up in counts for multiple divisions! I've also attempted the second approach using the max effective date in place of the RecordOrder calculated column, with no success. Any help would be appreciated!Filter on the right row for each user based on the timeframe
Hi guys, I have a mix of data coming from Google Analytics for web traffic which I want to join in with our own DWH to be able to get the best possible insights. So we have the following tables: 1 - Google Analytics - Traffic - It contains traffic on our website. The key here is user_id which can be joined with our DWH to find out more about this user/customer. 2 - Contact - SQL - It contains all our company customers/members in DWH. The key here is user_id to join with Google Analytics table. The table only contains there last status change. 3 - Contact History - SQL - This table contains historical changes for each customer/member in DWH. A customer can go from being a customer to becoming a member and later on go from being a member back to customer. For each change, there is a new row with a dbt_valid_from and dbt_valid_to date. What I am looking for: I want to count the number of distinct user_id in Google Analytics - Traffic for the selected dates in "Date" table and I would like to filter on the "active" row at that time. I want to make a line chart from Jan 2024 to September 2024. In this chart I would like to get the customer_type status for each user for each month. The dbt_valid_from and dbt_valid_to columns are the ones to tell when a row is valid or not. The slicer date must be between dbt_valid_from or dbt_valid_to date to select the correct. For current active status the dbt_valid_to can also be NULL as the rows is not expired yet. I am also attaching a shot from my model. Thanks for all help in advance. This is the DAX I have tried so far but it is not giving me the correct row: Active User Count = VAR SelectedDate = SELECTEDVALUE('Date'[Date]) RETURN CALCULATE( DISTINCTCOUNT('Google Analytics - Traffic'[user_id]), FILTER( 'Contact History', ('Contact History'[dbt_valid_from] <= SelectedDate) && ( ISBLANK('Contact History'[dbt_valid_to]) || ('Contact History'[dbt_valid_to] >= SelectedDate) ) ) ) What I am looking to visualise: I am basically looking for a visual like this. If a user is customer in January and turns member in July, I want it to be registered as a customer in January and member in July. Thanks in advance for all help.
