PowerBI DAX: How to calculate a running total for both date and another filter
I need to calculate a running total of a measure by date and by source. Below is a screenshot how my table looks like now.
Below is the measure code.
CashPosition_Revenue Running Total = CALCULATE([CashPosition_Revenue],FILTER(VALUES(FilterKeys_Date[Date]), FilterKeys_Date[Date] <= MAX(FilterKeys_Date[Date])))
I need last column to be calculated as running total.
1 answer

Using Values limits your filter context to the current date only, try using All to remove that filter:
CashPosition_Revenue Running Total = VAR CurrentDate = MAX(FilterKeys_Date[Date]) RETURN CALCULATE( SUM(FilterKeys_Date[CashPosition_Revenue]), FILTER( ALL('FilterKeys_Date'), FilterKeys_Date[Date] <= CurrentDate ) )
See also questions close to this topic

PowerBI: Total of Parent Without Total of Children in Matrix
I am new to Power BI. I have a matrix that looks like this.
Salesman is a hierarchy containing Salesman > Severity. So Salesman is the parent while Severity is the child. Measure is the Count of Serial Numbers for each SalesmanSeverity grouping.
It does not make sense to clutter the Matrix with rows such as George >315 days; but, at the same time, I want George to appear with a subtotal of 0. Does anyone know how I can make Severity = 0 disappear from the Matrix while preserving Salesman = 0 values?

Filtering by current fiscal year (date dimension) in DAX/Power BI
Apologies in advance  I'm fairly new to DAX and modeling in Power BI.
I have a date dimension table with a full date key column, fiscal year, fiscal quarter, etc., and a fact table with tests in regions. Part of my tracking involves a realtime count of tests in the current year and quarter by region. There's also a region dimension table.
My first approach was to just use SUMMARIZETABLES, grouping by Region and FY, with aggregated Q1Q4 calculated expressions, and allowing the user to filter ensuing visuals by FY. What I would like to do is have a separate query/table that only displays test counts for the current region/FY/quarter, which would involve DAX code looking up today's date and the current fy/quarter.
I thought I could use FILTER and LOOKUPVALUE:
Table = filter(summarizecolumns(dim_districts[district short name],DIM_Dates[Fiscal Year],"Facilities",COUNTROWS(relatedtable(FACT_Allocations)),"Tests",distinctcount(fact_quts[control id])),dim_dates[fiscal year]=lookupvalue(dim_dates[fiscal year],dim_dates[fulldatealternatekey],today()))
The problem is that I can't even reference dim_dates[fulldatealternatekey] since it's not referenced anywhere else in the table (as a groupby etc). Any help would be appreciated.

How use "New Parameter" in Power bi to calculate new Column
i'm trying to create a new Column that depend from 2 variables, the first is my value in Column "Data13.Value13" and the second is a dynamic parameter, I'm using "New Parameter" to Define my current value. After this i tried to use a simple cindition as Critical_Rain = IF(Historical_Data[Data_13.Values_13] < [Rain Value], 0 , Rain[Rain Value]), but PBI don't recognize my Parameter named "Rain" as value, and don't calculate my new column
How can i modify my code, or how i need to structure my parameter to do this simple operation?, Thanks

DAX. Obtain the value of a column based on the max value of another column
hope you are doing well.
I'm struggling with the following situation. I think it is easy to solve but I'm not reaching a solution because of my limited knowledge of DAX.
My scenario is the following:
I have 3 tables (I share a link below with an Excel test file so you can see it and work there):
1) "t_data" (where every register is a transaction of a spare part changed of a bus);
2) "t_actualKms" (where I have every bus with their respective actual kilometer). Related to "t_data";
3) and a "Calendar". Related to "t_data".
What I need is to calculate the "Actual performance" of each spare part in each bus, based on the last change of it and the actual kilometers of the bus.
The calculation (a measure) should be (if we use 2 other measures):
Actual performance = [Actual bus kms]  [Last spare part kms]
The [Actual bus kms] measure should take the kms from the t_actualKms table for each bus and use it to compare with the [Last spare part kms].
The [Last spare part kms] measure should take the last date a specific spare part was changed in an specific bus and bring the "Kms at change". With that then you will calculate the Actual performance.
Here is an Excel sample so you can test it and a tab with the goal (should be a matrix or a pivot table) of what I need.
Sample: https://drive.google.com/open?id=1a8LglB7F76SkmBgoneORWC0SZVZed9R7
Hope you can understand my problem. If you need more details please let me know.
Thank you very much in advance!!!
Andy.

Power BI DAX Count YM by the YM I selected in the filter
I have a measure that counts the year month in my dataset
the dax is like this:
CALCULATE( DISTINCTCOUNT(TABLE_NAME[YM]), FILTER(ALL(TABLE_NAME[YM]),TABLE_NAME[YM]<= MAX(TABLE_NAME[YM])) )
I would like it to change based on my filter, for example now if I choose 201908, 201909, 201910, it shows
I am looking for a way to make it be:
201908 1
201909 2
201910 3if this works, when I choose 201907, 201908, 201909, 201910, it should be like:
201907 1
201908 2
201909 3
201910 4I tried different ways but none of it works, please let me know if there's a way to solve it, thank you!

Cumulative sum with custom sorting in MySQL
I have a table that looks like this
id remaining expiry_date 1 200 20191115 2 10 20191123 3 10 20191116 4 10 20191116 5 7 20191116
I want to fetch the results that have a running total of 215 that is sorted by
expiry_date
inascending
order.What I am able to achieve so far?
SELECT *, @sum := (@sum + remaining) AS csum FROM tickets JOIN (SELECT @sum := 0) r WHERE @sum < 215 ORDER BY id;
This query returns the following result which is correct.
id remaining expiry_date csum 1 200 20191115 200 2 10 20191123 210 3 10 20191116 220
But when I try to sort it with
expiry_date
it returns all the records.SELECT *, @sum := (@sum + remaining) AS csum FROM tickets JOIN (SELECT @sum := 0) r WHERE @sum < 215 ORDER BY expiry_date;
Result:
id remaining expiry_date csum 1 200 20191115 200 3 10 20191116 210 4 10 20191116 220 5 7 20191116 227 2 10 20191123 237
The sorting is correct but the result is way more than I need.
What I want
I want to return the following result.
id remaining expiry_date csum 1 200 20191115 200 3 10 20191116 210 4 10 20191116 220
Also, the number 215 can change dynamically so the number of rows returned can vary based on that number. How can I change the query so I can achieve this?
Edit
I apologize for not being clear with what I actually wanted in my result set. Please let me clarify with this edit. I don't want the records with runningtotal less than the given amount. I want the records until the runningtotal is equal to or exceeds the given amount.

Pandas cumulative count by looking at if two lists have common value
If I have a table like this
  time  list of string    20190618 09:05:00  ['A', 'B', 'C']   20190619 09:05:00  ['A', 'C']    20190619 09:05:00  ['B', 'C']    20190620 09:05:00  ['C']    20190620 09:05:00  ['A', 'B', 'C'] 
For each row, I would like to know how many rows prior to the current timestamp have at least one common value to the current list of string.
The slow code would be something like this:
results = [] for i in range(len(df)): current_t = df['time'].iloc[i] current_string = df['list_of_string'].iloc[i] df_before_t = df[df['time']<current_t] cumm_count = 0 for row in df_before_t['list_of_string']: if (set(current_string) & set(row)): cumm_count += 1 results.append(cumm_count)
So the result table would be:
  time  list of string  result    20190618 09:05:00  ['A', 'B', 'C'] 0    20190619 09:05:00  ['A', 'C']  1    20190619 09:05:00  ['D']  0    20190620 09:05:00  ['C']  2    20190620 09:05:00  ['A', 'B', 'C'] 2  
The dataset I currently have is relatively large and I would like to get help to process this data fast. Thanks very much!