Running Total - Stop at a specific date
Hello. New to Power BI. I did a quick measure to create the running total for the "actual" data and it's show all months. How do I update the DAX below to stop at September but then update it to October and continue as real data is added? Thank you! Sum of Sub System Amount running total in Fiscal Month = CALCULATE( SUM('Combined'[Sub System Amount]), FILTER( CALCULATETABLE( SUMMARIZE( 'Fiscal Period Description', 'Fiscal Period Description'[Period], 'Fiscal Period Description'[Fiscal Month] ), ALLSELECTED('Fiscal Period Description') ), ISONORAFTER( 'Fiscal Period Description'[Period], MAX('Fiscal Period Description'[Period]), DESC, 'Fiscal Period Description'[Fiscal Month], MAX('Fiscal Period Description'[Fiscal Month]), DESC ) ) )
Calculate cumulative total by multiplying value to itself.
Hi. Asking for your help on calculating cumulative total by multiplying the average value to itself. I want to achieve the line chart below. My example is Id 1. For the data model the relationship of date table to Table 1 is inactive because there is another active relationnship in that table. For the sake of simplicity I just shown how End Date is connected to the date table as well as Start date from another table to the date table. Thank you in advance for your help.Cumulative Total of Two Measures from Separate Tables
Hi. I just want to ask a help on calculating cumulative totals based on two measures from separate tables. I have a data model below. What I did was, I created a measure for Table 1 and Table 2. Then I add them up by simpling doing addition Measure 1 + Measure 2. Let's call it Measure Total. Then I used the DAX below. However, I got a wrong cumulative value (see second image) Cumulative Total = Var maxDate = MAX(Calendar Table[Date] Return CALCULATE( Measure Total, ALL(Calendar Table), (Calendar Table[Date] =< maxDate))) Wrong cumulative Thank you in advance for any help you can give.
Cumulative Total from a Summarised Table
Hello All, I have the followed the information provided in this thread to create a summary table with a cumulative total here: https://community.fabric.microsoft.com/t5/Desktop/Cumulative-Using-Summarized-Table/td-p/1355321 However my cumulative value will not result and I don't understand why. It should be the cumulative of the Incremental Sales column. I expect the cumulative to be 80764+208746 etc. The Incremental Sales is simply 2024-2023 month. What am I doing wrong? Thanks
Cumulative total with condition
Hi, I m trying to get cumulative total of "Forecast Gross" once "Closing stock" end e.g. Last Closing stock date is 18/03/24 so Forecast should start cumulating from 18/03, I was able to calculate demand after last closing stock date but it is not cumulating. below is the measure. Demand Cumulative:=VAR LastClosingStk = CALCULATE(MAX([Transaction Date]),'Date'[DateWithSales] = TRUE,ALL('Date')) VAR Result = CALCULATE([Forecast Gross],FILTER('Date','Date'[Date]>=LastClosingStk)) Return Result https://1drv.ms/x/s!Agco7LD2yJqrpXn-v3X8sw9g36vt
Cumulative Cost Measure - Line Graph Stop Flat Lining Out
Hi there! I have created a cumulative cost measure within one of my reports which is cumulating cost values over a 15 year period however I am not so happy with the visual which I will explain. My data is split into 3 Cost Types (Legend) - Earned, Planned, To Go. All three are showing fine on the line graph & 'Planned' will remain the same and show the trajectory over the 15 year span, however my value for 'Earned' is projecting until the current reporting month and then it is just flatlining as the cumulative value is remaining the same. I want this line to stop on the reporting month and when nothing further is cumulating to the 'Earned Value'. Also on the 'To Go' Value this works in the opposite and is only starting to project from the reporting month, and flatlining at 0 up until then so I would really want this line to not show until the reporting month and start projecting from there onwards. Any tips and suggestions would be greatly appreciated, below is the current DAX I am using to cumulate my values. Cumulative Cost = CALCULATE( SUM('EV Data Pivot'[Month Value]), FILTER( ALLSELECTED('EV Data Pivot'), 'EV Data Pivot'[Month] <= MAX('EV Data Pivot'[Month]) ), VALUES('EV Data Pivot'[Control Account]), VALUES('EV Data Pivot'[Cost Type]) ) ----- (Also may be worth noting I have replaced my date hierarchy on X Axis with a custom dateTable relationship for text values for FY, Quarter, Month to align with financial year reporting.)
Cumulative Total data points with no data (0s) display as the maximum issue
I am trying to develop a Ticket Sales cumulative line graph sectioned out by how many weeks before the event the tickets were bought. I used the following DAX command to get the cumulative total. And here is the graph that results from this measure. The issue that appears is the data points of 0 display as the maximum, or what is known as the final total at 0 Weeks Out from the event. What is even more confusing is that the cumulative total measure recognizes those data points as 0s because let's say the final total is 100; those 0s show up as 100 but the measure does not count them because it knows they are 0s. This problem is even more apparent when I insert a legend. All of these spikes are weeks where no tickets were sold, and instead of maintaining the past week's total, they spike to the maximum (final total) and are not included in the measure's calculation. I have also considered excluding these data points that spike, since they would be 0 anyway, but there are over 1500 that would need to be excluded and after 50, PowerBI gives me an error. Is there anything I can include in my DAX command to make these "spikes" smooth out and carry over the past week's total?
cumulative count measure per row
hello i need to create a measure that calculates incentive as per below rules Achievement Incentive 1 to 5 count of sales 2$ 6 to 10 count of sales 4 $ 11 to 15 count of sales 6 $ 16 and above count of sales sales 8 $ what this means, if agent has made 15 transactions, he will get incentive of (2$*5) for first 5 transactions, and (4$*5) for next 5 transactions and (6$ *5) the 5 transactions after. so total incentive= 10+20+30=60$ i created below measure = CALCULATE(IF([MNP cumulative count]<=5,[MNP cumulative count]*2,IF([MNP cumulative count]>=6 &&[MNP cumulative count]<=10,[MNP cumulative count]*4,IF([MNP cumulative count]>=11 &&[MNP cumulative count]<=15,[MNP cumulative count]*6,IF([MNP cumulative count]>=16,[MNP cumulative count]*8,0))))) and [MNP cumulative count]= CALCULATE(SUMX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE],'categories trans'[achievement],'categories trans'[Categoty]),[mnp distinct count]), FILTER ( ALLSELECTED('categories trans'), 'categories trans'[TRANSDATE]<=MAX('categories trans'[TRANSDATE]))) my problem is the cumulitve count does not count correctly when agent has 2 transactions in the same day. instead of counting them as 2 it's counting them as 1 also the data i have is as below table agent id agent name transaction value (achievement) date 123 abc 2 1/11/2023 456 def 1 2/11/2023
Cumulative total - stopping after last date not working with hierarchy
Hi - i've created a measure as shown here to create a cumulative sum up to the last sales date, then to give a blank after that date: Cml Actual Invoices = VAR LastSalesDate = CALCULATE(LASTDATE(f_scoro_SalesInvoicesActual[Date Invoice]),ALL()) RETURN IF( SELECTEDVALUE(d_time_Dates[Date]) > LastSalesDate, BLANK(), CALCULATE([Sum of Actual Invoices], FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date])))) It shows correctly if my visual is operating at the single date level: However I need to be able to move up / down a date hierarchy and for the measure to work, which is currently is not doing, as shown below if i move up to Month granularity, the meaure no longer returns a blank after the last sale date, and repeats the cumulative total for months beyond the last date: I think I understand why, becuase in my measure I'm calculating based on the full date key and not the month, however I don't know the solution. Any help would be appreciated. Thank you, Joe
