In PowerBI, I want to create a calculated table from table t. Using DAX, how can I write the equivalent of the following SQL query. Basically, here I am finding running sum (cumsum) using cnt ordered by date. select date, cnt, sum(cnt) over (order by date) as cumsum from t This is an example output.… Read More How to apply order by Window function in DAX
I have a table with multiple companies. Shop Currency S1 € S2 € S3 kr S4 £ S5 € And a table with the exchange rate Currency Exchange rate € 1.00000000000 £ 1.17100000000 kr 0.09730000000 When using a formula like this YearRevenue = SUM(Shop[Revenue]) * SUM(Currency[Exchange rate]) Logically what it now does is add all… Read More Dashboard to compare different companies with different currencies
I simply want to count rows consisting number "1" and "2" in a column, I used the following measure: First time fix = CALCULATE(COUNTROWS(TICKET), TICKET[num_replies]=1 && TICKET[num_replies]=2 , USERELATIONSHIP(DIM_DATO[Opprettet], TICKET[Created])) But it returns blank, how should I change the syntax in order to return a number? >Solution : Use OR in place of AND First… Read More Countrows with two filters
Here my table: When I create a summed table by group_no I have below table in Power BI: However, this is what I would like to have: So, what I would like to get is total revenue by unique project_no on Groups (group_no). >Solution : Here you go: aggregated_revenue = VAR tbl = SUMMARIZE(‘Table’, ‘Table'[project_no],… Read More Aggregate values by a distinct identifier in Power BI using DAX
I have a table of the following format: I need to calculate a "Forward Rate" measure for each row, which = Discount Factor Current Month / Discount Factor Proceeding Month – 1. I have tried the following measure but it does not work: Forward Rate = SUMX(facYieldCurves, IFERROR( DIVIDE(LOOKUPVALUE(facYieldCurves[Discount Factor], facYieldCurves[Month] , [Month] – 1)… Read More Calculate a forward rate using measure
I need a way to capitalize the first letter of the month in date hierarchy to use them in a matrix. I tried to do the following column: month = SWITCH(TRUE(), MONTH(‘table'[date]) = 1, "January", MONTH(‘table'[date]) = 2, "February", MONTH(‘table'[date]) = 3, "March", MONTH(‘table'[date]) = 4, "April", MONTH(‘table'[date]) = 5, "May", MONTH(‘table'[date]) = 6, "June",… Read More Is there any way of capitalize the first letter of the month in date hierarchy?
I’m relatively new to DAX and I believe there should be straight forward solution for this problem. I have ID for customers and corresponding periods, for which we have orders for them. These are not in particular order, so I need to add a column in which, for each ID, it shows me what is… Read More Numbering order of Dates for Dimension in other Column
I stumbled upon this piece of DAX to calculate the 12-day moving average from a table (‘Stock’) that has [Date] and [Close] columns: MA_12 = AVERAGEX( DATESBETWEEN(Stock[Date], MAX(Stock[Date]) – 11, MAX(Stock[Date])), CALCULATE(SUM(Stock[Close])) ) I am relatively new to DAX and trying to decipher what is happening behind the scene. I tried to write my own… Read More Understanding the DAX expression for moving averages
I have a dax formula which counts rows based on dates: Avsluttet = CALCULATE(COUNTROWS(TICKET),USERELATIONSHIP(Dato[Opprettet dato], TICKET[closed_at])) But the column closed_at cointans "null" data that I want to exclude. How do I implement filter in the code above, so it only count rows containing dates? I have to keep "null data" in this column because it… Read More USERELATIONSHIP together with FILTER in CALCULATE
I have 3 columns where there will only ever be a value within one of the three columns. I’m trying to get that value into a single column to make the reporting easier. Can anyone see where I’m going from with what I have below? WhichForm = SWITCH ( TRUE, ISBLANK([Column 1]) = FALSE, [Column… Read More Use column that has a value within it – ignore blank columns