12 Year Min/Max for running total
Hello, I am using a running total measure to calculate the current month, the prev month, and the same month last year. The challenging part is creating the Range for the past 12 months. How can I calculate the 12 month min and the 12 month max? Here are my measures for the current and previous month: Current = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _sum = CALCULATE(SUM(Table[Volume]), Dates[Day Number] = _day) RETURN IF(ISBLANK(_sum), BLANK(), CALCULATE( SUM(Table[Volume]), FILTER( ALLSELECTED(Dates[Day Number]), ISONORAFTER(Dates[Day Number], MAX(Dates[Day Number]), DESC) && Dates[Day Number] <= _day ) )) LastMonth = VAR _day = SELECTEDVALUE(Dates2[Day Number]) VAR _month = SELECTEDVALUE(Dates[PrevMonthYearDate]) VAR _sum = CALCULATE(SUM(Table[Volume]), ALL(Dates), Dates[Day Number] = _day && Dates[MonthYearDate] = _month) RETURN IF(ISBLANK(_sum), BLANK(), CALCULATE( SUM(Table[Volume]), FILTER( ALL(Dates), ISONORAFTER(Dates[Day Number], MAX(Dates[Day Number]), DESC) && Dates[Day Number] <= _day && Dates[MonthYearDate] = _month ) )) The dataset is simple. I use 3 tables, the Data table with Dates, Volumes, and Locations. The other 2 tables are Date tables. I use 2 date tables because the amount of days in the selected month may not be complete or will have less days than the previous month so the days after would not show. (The yellow line after the end of the blue line won't show if I didn't use 2 date tables.)
Running total in Dax Studio
Hi There, Could anybody held with this Dax Query. I Group Sales by month and created a raking, as following: DEFINE VAR Sales = ADDCOLUMNS ( VALUES ( 'DIM PRODUCTO'[PRODUCTO]), "@SalesMonth", CALCULATE([VENTAS EN UNIDADES], 'DIM TIEMPO'[AÑO-MES]="2024-03") ) EVALUATE ADDCOLUMNS ( Sales, "Rank", RANKX ( Sales, [@SalesMonth] ) ) ORDER BY [@SalesMonth] DESC This is the result: Is there any way to do a running total based on this result? Thanks
Running total on a large scale dataset
Hi all, I'm having a 80 million record dataset. When I was doing the POC report that has only account number, I used the following fomular to generate my balance: Balance = [Opening balance] + CALCULATE( SUM(transaction[Amount]), FILTER( ALLSELECTED(transaction), [Index] <= MAX(transaction[Index]) ) ) The above fomular works exactly as what I want. However, when I load not all my data, but just over 10 million data, this fomular gives me an error message saying that it's running out resources. Then I thought about dividing my original transaction table into pieces first as for my report, anyway, one account number and only one account number must have been selected. I used the following fomular to divide my table: FilteredTable = CALCULATETABLE( 'transaction', FILTER ( 'transaction', 'transaction'[Account_number] = SELECTEDVALUE('transaction'[Account_number]) ) ) It gives me an empty table, if I replaced "SELECTEDVALUE('transaction'[Account_number])" by a fixed text string, new table will be created with data. Anybody can help me on this issue? Thanks in advance.
Running total across years not showing correctly
I am trying to build a visual based on data from SAP with actuals on posting data. I have used running total measure to display a cumulative graph over time. However, it either shows non-cumulative or cumulative but not sorted by proper month/year. I would like the graph to display cumulative actuals over time (months and years) based on a CJI3 report from SAP. What am i doing wrong? See pictures attached. Thank you kindlyDax Running Total working excepts for months with no values
Hi, I have this measure that I have created that works fine with one caveat.. When using the measure on the y=axis and 'dim date'[month name] on the x-axis it display correctly, however.. When a filter is applied to the 'supported business' table for sector this reduces the running total amount correctly but for any months where the chosen sector value doesnt have a value the the x axis doesnt show that month, skips it and continues to the next month.. here is image with no filter applied.. here is mimage with sector filter applied.. and here is my measure.. A SB Cumulative Deal Amount Previous FY = Var maxdate = calculate( max('Supported Business'[RR If Cp Else GI Date]) ,'Supported Business'[RR If Cp Else GI Date] <= date(2023,3,31) ) Var Result = calculate( sum('Supported Business'[RR Liability GBP]) , allselected('Supported Business') ,'Supported Business'[RR If Cp Else GI Date] <=maxdate ,'Supported Business'[RR If Cp Else GI Date]> date(2022,3,31) ) return Result This measure gets the total for the previous financial year so the inital var MaxDate is getting the the last day of sales for that financial year. I am using the date column [RR If Cp Else GI Date] and getting anything <=maxdate to get the running total. I have tried to use the 'dim date'[date] column as always suggested in any examples ive rad on help forums but could never get that to work. Is there something i can apply to my current code withou changing the measure to use 'dim date'[date] so that we dont skip a month on the x axis if no sales but just replicate the previous running total value? Thanks, RCreate a running total based on another measure
Hello everyone - I need a measure that provide a running total (RT) based on the result of another measure (Shipped). Below my data set and the current output (measures below) Date Paid Qty Shipped RT 11/1/2021 2 0 0 12/1/2021 2 0 4 1/1/2022 2 0 0 2/1/2022 2 0 0 3/1/2022 12 12 16 4/1/2022 12 0 28 5/1/2022 14 14 42 6/1/2022 14 0 56 7/1/2022 14 0 70 8/1/2022 17 17 87 9/1/2022 17 0 104 10/1/2022 17 0 121 11/1/2022 17 0 138 12/1/2022 17 0 155 Where [Paid Qty] CALCULATE ( SUM ([Qty] ), Data[Transaction] = "Paid" ) Where [Paid PM] CALCULATE ( SUM ( [Qty]), DATEADD ( Data[Fiscal Date], -1, MONTH ) ) [Shipped] IF ( [FM ID] = 0, --First month is excluded, this Measure is MAX(FiscalDateID) 0, IF ( AND ( [Paid Qty] > [Paid Qty PM], [Paid Lics] > 0 ), [Paid Qty], 0 ) ) [RT] CALCULATE ( [Shipped], DATESYTD('Data'[Fiscal Date]) ) The output I'm looking for is Date Paid Qty Shipped RT 11/1/2021 2 0 0 12/1/2021 2 0 0 1/1/2022 2 0 0 2/1/2022 2 0 0 3/1/2022 12 12 12 4/1/2022 12 0 12 5/1/2022 14 14 14 6/1/2022 14 0 14 7/1/2022 14 0 14 8/1/2022 17 17 17 9/1/2022 17 0 17 10/1/2022 17 0 17 11/1/2022 17 0 17 12/1/2022 17 0 17 I can't figure this one out, so I'd really appreciate some help!
