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.Microsoft Report builder YTD Calculation Error
Hi all, Ive been struggling with an issue for a long time in Microsoft Report Builder and have not found anything that worked for my model. I have created a very simple dataset to replicate the problem I am having. I have a powerbi dataset connected to Microsoft Report Builder through dax studio My data model has 3 columns: - Region - Date - Amount I would like to display a YTD value If i selected Jan 2023, the YTD measure returns the correct value If i select Jan 2023 and Feb 2023, the YTD measure returns the correct value If i select Jan 2023, Feb 2023 and March 2023, the measure returns the correct value If i select Feb 2023 and March 2023, the measure returns incorrect value - It returns the sum of the periouds in the filter selected. I need it to return the YTD for the max date in the filter selected The dataset and report builder files can be found here Any help would be greatly appreciated
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, R
