Week over week measure
Hi, I have created this "# Open tickets EOP" measure and it is working and looks like this below: OpenTasksWoW = VAR MinDate = MIN ( 'dim_date'[Date] ) VAR MaxDate = MAX ( 'dim_date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( CurrentRow_tasks ), CurrentRow_tasks[taskCreatedDateDK] <= MaxDate, OR( CurrentRow_tasks[taskClosedDateDK] > MinDate, ISBLANK(CurrentRow_tasks[taskClosedDateDK]) ), REMOVEFILTERS ( 'dim_date' ) ) RETURN Result # Open tickets EOP = CALCULATE ( [OpenTasksWoW], LASTDATE ( 'dim_date'[Date] ) ) But now I also want to calculate the days the ticket is open week over week. This measure is not working because it shows only a value for the first week a ticket is open. DaysOpen = VAR CreatedDate = MAX(CurrentRow_tasks[taskCreatedDateDK]) VAR ClosedDate = MAX(CurrentRow_tasks[taskClosedDateDK]) VAR LastDateInPeriod = LASTDATE('dim_date'[Date]) RETURN IF( ISBLANK(ClosedDate), DATEDIFF(CreatedDate, LastDateInPeriod, DAY), IF( ClosedDate <= LastDateInPeriod, DATEDIFF(CreatedDate, ClosedDate, DAY), DATEDIFF(CreatedDate, LastDateInPeriod, DAY) ) ) I want a value for EVERY week a ticket is open. So for every end of period it should calculate the days it is open. In this example: INCIDENT798633 should have value 11 for Year_Week_Numeric = 202506, value 18 for Year_Week_Numeric = 202507, value 25 for Year_Week_Numeric = 202508, etc.
Ignore filter on my measure
Hello, I’m using a Sparkline generated via an API, and it’s working perfectly. Each dot corresponds to a week, allowing me to see the variation over time from the start of my dataset. However, I’ve encountered a challenge: I want to filter my page data (e.g., using a slicer) to focus on specific weeks, such as Week 31 of the year. This should dynamically update all visuals on the page. However, I also want the Sparkline visual to always show the total data (ignoring any filters) to provide a full historical view. I can’t disable the interaction between the filter and the Sparkline visual because I’m also using a card and a main measure on the page. These elements need to reflect the filtered data based on the selected date range. Here’s the current code for my Sparkline: As an example, here's how it looks like when no filter is applied : And here is how it looks like when I filter on 3 weeks : The data change to 5484 which is great but my sparkline also changes as well, I would like it to stay unfiltered. Thank you in advance.
Previous 2 year values for current top 7 countries and others
I want to show the sales of TOP 7 countries for the selected year and the remaining sales I need to add up the country and show it as "Other" in Country Name column. After that, Based on current top 7 countries names i need to compare with previous last two years same country sales amount. Calendar Country Name Total 2024 Qatar 18.98 2024 Australia 15.28 2024 Malaysia 12.2 2024 UK 9.64 2024 United States 4.56 2024 Netherlands 3.32 2024 Canada 1.28 2024 Others 17.4 Expected Ouput: Comparing current top 7 countries with previous year Calendar Country Name Total 2024 Qatar 18.98 2024 Australia 15.28 2024 Malaysia 12.2 2024 UK 9.64 2024 United States 4.56 2024 Netherlands 3.32 2024 Canada 1.28 2024 Others 17.4 2023 Qatar 20.98 2023 Australia 17.28 2023 Malaysia 16.2 2023 UK 10.64 2023 United States 9.56 2023 Netherlands 8.32 2023 Canada 6.28 2023 Others 19.4 2022 Qatar 22.98 2022 Australia 17.98 2022 Malaysia 13.2 2022 UK 11.64 2022 United States 10.56 2022 Netherlands 9.32 2022 Canada 7.28 2022 Others 19.76 Measure used: Total Sales = VAR _start= Year(MAX ( 'Calendar'[Date] )) VAR _end = _start - 2 CALCULATE ( ROUND( CALCULATE(Sales amount),2), ALL ( 'Calendar' ), FILTER ( 'DATE', year('DATE'[DATE_TIMESTAMP_DTM]) <= _start && year('DATE'[DATE_TIMESTAMP_DTM]) >= _end ) ) This measure i used to calculate top 7 and others, and also to display last 2 year sales value comparsion with current year sales values of the top 7 countries Top7= VAR Top_N = 7 VAR TOPNCOUNTRY = TOPN(Top_N, ALL('COUNTRY'), [Total Sales] ) VAR ALLCOUNTRY = CALCULATE( [Total Sales],ALLSELECTED('COUNTRY')) VAR OTHERCOUNTRY = ALLCOUNTRY - CALCULATE( [Total Sales,TOPNCOUNTRY) VAR TOPNCOUNTRYDIS = CALCULATE( [Total Sales],KEEPFILTERS(TOPNCOUNTRY)) VAR CURRENTPCOUNTRY = SELECTEDVALUE('INTENSITY COUNTRY'[COUNTRY]) RETURN IF( CURRENTPCOUNTRY = "Others", OTHERCOUNTRY, TOPNCOUNTRYDIS ) Problem: Previous year values for current top 7 countries name is not happening instead each year it displays top 7 and others
Measure is Turning Blank on Table
Hi, I can calculate the diagonal values according to 2 different dates, but my aim is that create this measure for a matrix with row "customer" and one of the dates. Here is the sample data, StartYear StartMonth CalcYear CalcMonth Customer fixedfee 2023 1 2023 1 100 0.00 2023 1 2023 2 100 0.00 2023 1 2023 3 100 0.00 2023 1 2023 4 100 0.00 2023 1 2023 6 100 0.00 2023 1 2023 7 100 41.07 2023 1 2023 8 100 115.93 2023 1 2023 9 100 62.23 2023 3 2023 3 100 0.00 2023 3 2023 4 100 0.00 2023 3 2023 6 100 0.00 2023 3 2023 7 100 41.07 2023 3 2023 8 100 57.97 2023 5 2023 5 100 0.00 2023 5 2023 11 100 77.95 2023 6 2023 6 100 0.00 2023 6 2023 7 100 82.13 2023 6 2023 12 100 100.88 2023 6 2024 1 100 131.92 2023 7 2023 7 100 1,190.93 2023 7 2023 8 100 289.83 2023 7 2023 9 100 248.93 2023 7 2023 10 100 329.81 2023 7 2023 11 100 155.91 2023 7 2023 12 100 403.53 2023 7 2024 1 100 263.85 2023 8 2023 8 100 1,681.03 2023 8 2023 9 100 622.33 2023 8 2023 10 100 329.81 2023 8 2023 11 100 311.82 2023 8 2023 12 100 201.76 2023 8 2024 1 100 263.85 2023 9 2023 9 100 1,867.00 2023 9 2023 10 100 593.65 2023 9 2023 11 100 77.95 2023 9 2023 12 100 302.65 2023 9 2024 1 100 131.92 2023 10 2023 10 100 1,715.00 2023 10 2023 11 100 467.73 2023 10 2023 12 100 201.76 2023 10 2024 1 100 131.92 2023 11 2023 11 100 1,715.00 2023 11 2023 12 100 201.76 2023 11 2024 1 100 395.77 2023 12 2023 12 100 1,715.00 2023 12 2024 1 100 263.85 The measure that calculates the diagonal values below, turns blank on the matrix when "customer" is on row and "calcdate" is on column, FF_Agg = VAR startyear=SELECTEDVALUE(table[StartYear]) VAR startmonth=SELECTEDVALUE(table[StartMonth]) RETURN CALCULATE ( SUM(table[FixedFee]), FILTER(table,startyear=table[CalcYear] && startmonth=table[CalcMonth]) ) It will be dynamic table under the date filters so, I need a measure instead of calculated table, my aim matrix to reach out, CalcYear 2023 Customer CalcMonth 1 2 3 4 5 6 7 8 9 10 11 12 100 0 0 100 0 100 100 1190 1681 1867 1715 1715 1715 Thank you, VeliDAX measure for the column/pie/ charts to color only the max value bar/slice
Basically title. Say I have a bar / column chart with the maximum count of category having 5, while others have lower. I want this particular bar to be color with specific color, while other bars should have the same color.
TotalYTD or DatesYTD based on SLicer filter
Hello everyone, I have a totalytd measure, and creating a bar chart based on date and total sales. Also I have a slicer which contains Month Name. If I filter for March month, the bar chart will show data only for March month but my requirement should be like the visual should display till March month (Jan, Feb and Mar). Likewise if I select June month in the slicer, the bar chart should display data from January to June. How to achieve this, please advice.Commission Measure
Hi Everyone, I need help building a commission measure. I've found a lot of references which filter the Commission Tier and then multiplies the current sales * max commission percentage, but our company works differently (don't they all). I have a table of Weekly Sales by Employee (AccountManager), table detailing each employee's Commission Tier (not referenced below EE_ComTier), and a final table of the specifics of each Commission Tier (Name, Low, High, Rate) Take an Account Manager earning 12,000 in sales for a given week and an example of our commission structure below Using the code below results in 12,000 * 0.1 = 1,200 which is incorrect. This is the correct calculation: 3000 * 0.05 + (6000-3000.01)* 0.075 + (10000-6000.1) * 0.1 + 2000 * 0.125 Total Coms = 1,025 The weekly sales amount gets deducted through each commission bucket. I tried to adapt the below measures which has gotten me partially to the result I need (max rate * weekly sales). RateTest = VAR Sales = [AM Sales] Return CALCULATE(MAX(dimComTiers[Rate]), FILTER(dimComTiers, dimComTiers[SalesLw]<=Sales &&dimComTiers[SalesHg]>=Sales &&dimComTiers[Tier]=VALUES(AccountManager[Tier]))) ComTest = VAR Sales = [AM Sales] Return SUMX(AccountManagers,SalesTable*[RateTest]) If anyone has any thoughts, I'd be very appreciative thank you!
