cumulative measure in line chart
Hi everybody, I'm stuck with an issue that is bothering me for quite some time now. The orange line in the line chart below represents the cumulative written hours n a project. The hours are registered untill july, after that there is no data. As you see, the line continous untill the end of the year. I want to create a dax measure (or another solution if that's easier), that cuts off the orange line after the the point to which there is no more data available. Currently I use this measure: Cumulative_Geschreven Uren = CALCULATE(SUM('Urenregistratie'[Geschreven uren]), FILTER(ALLSELECTED( DateTable), DateTable[Date] <= MAX( DateTable[Date]))) ChatGPT recommened me this measure, but in this case the line cuts off in June, while there is data from the month July (see picture below). In this case i used the following DAX. Cumulative_Geschreven Uren (Cutoff) = VAR CurrentDate = MAX(DateTable[Date]) VAR LastDateWithData = CALCULATE( MAX(DateTable[Date]), FILTER( ALLSELECTED(DateTable), CALCULATE(SUM('Urenregistratie'[Geschreven uren])) <> 0 ) ) RETURN IF( CurrentDate <= LastDateWithData, CALCULATE( SUM('Urenregistratie'[Geschreven uren]), FILTER( ALLSELECTED(DateTable), DateTable[Date] <= CurrentDate ) ) ) Any solution would be greatly appreciated! Willem
Cumulative count with status
Hi, I am new here but I have been a user of the forums for long time. Hats off to all the lovely people out there who is helping others with the answers. Here my question: I have requirement with cumulative count of approved connectors by different approval status and by quarter which should show only last 4 quarters inlcuding the present quarter if there is no data for any quarter it has to take value of previous quarter and present that. I have connectors table like below connectorid createddate approvaldate approvalstatus 1 30/09/2016 20/06/2022 niche 2 18/7/2017 10/07/2022 standard 3 10/10/2016 10/07/2022 standard 4 10/03/2018 28/08/2022 standard 5 10/10/2016 30/10/2022 standard 6 10/03/2018 01/02/2023 niche 7 21/05/2019 10/03/2023 standard Expected result is Quarter Status Totals for Status Cumulative for quarters Q3-2022 niche 1 4 Q3-2022 standard 3 4 Q4-2022 niche 1 5 Q4-2022 standard 4 5 Q1-2023 niche 2 7 Q1-2023 standard 5 7 Q2-2023 niche no data - copy from previous quarter no data - copy from previous quarter Q2-2023 standard no data - copy from previous quarter no data - copy from previous quarter If i get the data like this above then my Line and stacked column chart will give Q3-2022 - column bar with 4 with a division of 1 and 3 niche and standard Q4-2022 - column bar with 5 with a division of 1 and 4 niche and standard I have done the measure for total status = COUNT(Connectors[Status]) Total Cumulative Connectors = CALCULATE( Connectors[total status], FILTER( ALL(Connectors[approvaldate]), Connectors[approvaldate]<= MAX(Connectors[approvaldate]) ) using the above 2 measures, its giving same for total status and Total Cumulative Connectors. also it doesn't give me like the table which I am expecting above. it gives Quarter Status Total status Total Cumulative Connectors Q3-2022 niche 1 1 Q3-2022 standard 3 3 Q4-2022 standard 1 1 Q1-2023 niche 1 1 Q1-2023 standard 1 1 Q2-2023 Sorry about the long post, it will be great, if anyone can help me getting the desired result. These are all made up numbers, as I can't share the data because of confidentiality. Thank you.Help Needed with DAX Expression for Current and Future Month Calculations
Hi everyone, I’m working on a DAX expression to calculate a display value for a line chart in Power BI. My goal is to correctly handle data for the current month and future months with the following requirements: For the current month: I need to combine the cumulative sum of actual values with the rolling wave forecast value. For future months: The display value should consist of the cumulative sum of the actual values from the current month plus the forecast value for that future month. Here's the DAX expression I have so far: Display Value = VAR IsCurrentMonth = YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()) VAR IsFutureMonth = YEAR([Date]) > YEAR(TODAY()) || (YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) > MONTH(TODAY())) VAR CurrentMonthActualCumulativeSum = CALCULATE( MAX([Actual Cumulative.Sum]), FILTER( ALLSELECTED(), YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()) ) ) RETURN IF( [Series Type] = "Rolling Wave Forecast", IF( IsCurrentMonth, [Actual+Rolling Wave Forecast], IF( IsFutureMonth, CurrentMonthActualCumulativeSum + [Value], [Actual+Rolling Wave Forecast] ) ), [Value] ) The Problem: When I add this measure to a line chart, the current month’s values display correctly. However, for future months, the cumulative sum is incorrectly added multiple times. For example, in the next month, the cumulative sum of the actual values from the current month is added again, and this continues to accumulate incorrectly in subsequent months. What I Need: I need the calculation to add the cumulative sum of the actual values only once for the current month and then add the forecast value for each future month without repeating the cumulative sum. Any guidance on how to fix this issue would be greatly appreciated! Thank you! (Images with captions are below)DAX Cumulative Total incremented by this total
Hi, Has anyone a idea how to solve this situation in DAX. In Excel it is quite easy becasue you can always refer to a specific cell. I need to increment the A Value by B Value, and if A is Blank the A increment should happen by the previous result of the calculation. Thank You in advance!Cumulative sum on summarize table
Hi All, I have a summarize function from the below code: VAR v1 = SUMMARIZE( FILTER( FactSales, FactSales[AsOfDate] = CALCULATE(MAX(FactSales[AsOfDate]), ALL(DimDate)) ), DimDate[Year], "Last As Of Date", MAX(FactSales[AsOfDate]), "Last Sales", LASTNONBLANK(FactSales[Sales], 0) ) Which provides this output Year Last As Of Date Last Sales 2023 28-02-2023 00:00:00 1037467263,3053 2022 31-12-2022 00:00:00 2132113394,1337 2021 31-12-2021 00:00:00 2726595255,5036 2020 31-12-2020 00:00:00 4503000000 2019 31-12-2019 00:00:00 4372000000 2018 31-12-2018 00:00:00 3985000000 2017 31-12-2017 00:00:00 3681000000 2016 31-12-2016 00:00:00 3101000000 2015 31-12-2015 00:00:00 2607000000 2014 31-12-2014 00:00:00 2245000000 2013 31-12-2013 00:00:00 2144000000 2012 31-12-2012 00:00:00 1443300000 2011 31-12-2011 00:00:00 1470900000 2010 31-12-2010 00:00:00 3486451000 2009 31-12-2009 00:00:00 3520772000 What I then need is a cumulative sale based on the above table, but I cannot figure it out. I have tried using the SUMX function but it does not seem to work. Can someone help me out on this? Best Regards, SolleCumulative Calculation Multiplying Values from Previous Row
Hello, I need to create a cumulative measure that references the previous rows values like this: Where refPeriod comes from the dim table Periods, allItemsByItem comes from the fact table I_data and value comes from another fact table W_data. The relationships between tables are as follows: Periods[refPeriod] = I_data[refPeriod] 1 to many single direction Periods[basketYear] = W_data[basketYear] many to many bi-directional There is no relationship between I_data and W_data Slicers for product, geography, start refPeriod and end refPeriod are used to bring the results into a table visual. I can identify the first selected reference period with if(min('Periods'[refPeriod]) = selStartDate, value..... where selStartDate is a measure that captures the value from one of my date slicers. The problem is how to figure out the calculation after the first row. First row logic: = value from the same row. All other row logic: = the result of the measure in the previous row multiplied by the allItemsByItem in the previous row. Any suggestions would be greatly appreciated. And I can provide more information if it's needed.DAX Optimisation Cumulative DISTINCTCOUNT
Hi All, I am wondering if someone can help me with a very slow DAX calculation. Business Case: We consider a customer to be financially active on our system for a financial year if the sum of their transactions for a financial year for any Group ID is greater than 0. Our financial year lasts from 1st of August and ends 31st of July. Product ID's are grouped under a Grouping ID. DAX I created a grouping calculated column in the customer table, (Fiscal Year || Grouping ID || Customer ID) { to avoid having to create joins in the query step). I want to calculate the number of financially active customers ( Active Customer Count ) and a cumulative count of this metric (Cumulative Active Customer Count). However the cumulative sum is very slow (20-30 seconds long) Active Customer Count = CALCULATE( DISTINCTCOUNT('Customer Tbl'[Customer ID]), FILTER( ALL('Customer Tbl'[Fiscal Year || Grouping ID || Customer ID]), [Transaction Amnt]>0)) Cumulative Active Customer Count = CALCULATE( [Active Customer Count], CALCULATETABLE( DATESYTD('Dim Date'[Date], "31-07"), 'Dim Date'[Is Future Date] = "Not Future Date" ) ) Any idea on what I can do to speed up this cumulative count? Link to file: https://drive.google.com/file/d/18OuoPSx2pFmk0Q-NCU1hhPcFyt_pQvr3/view?usp=sharing
Cumulative monthly count
Hi all, Couldn't get my "Cumulative Mthly Leavers" column to work. Would appreciate your guidance, please. Leavers = CALCULATE( [Count of Employees], USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Term date]) ) + 0 Cumulative Mthly Leavers = CALCULATE ( [Leavers], FILTER ( ALLSELECTED ( 'Calendar'[Month] ), 'Calendar'[Month] <= MAX ( 'Calendar'[Month] ) ) ) Thanks! VinCumulative measures count
Hi, I'm trying to get my "cumulative Leavers" column working but to no avail. "Cumulative Leavers" simply calculate a running total of the "Leavers" column. Please help! My measures are as follows: New Hire = CALCULATE( [Count of Employees], USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Hire date]) ) Cumulative New Hire = CALCULATE( [New Hire], FILTER( ALL('HR Dataset'), 'HR Dataset'[Hire date] <= MAX('Calendar'[Date]) ) ) Leavers = CALCULATE( [Count of Employees], USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Term date]) ) + 0 Cumulative Leavers = CALCULATE( [Leavers], USERELATIONSHIP('Calendar'[Date], 'HR Dataset'[Term date]), FILTER( ALL('HR Dataset'), 'HR Dataset'[Term date] <= MAX('Calendar'[Date]) ) ) My model is as follows: I suspect it's relationship-related but counldn't pin point where the issue is. Your help will be much appreciated! Vin
