Running Total and shifting the date back by one month.
Hi, I’d like to seek your help to fix my issue in DAX measure with calculating the Running total with shifting date back by month. This is the DAX code for Running total (BX column) MEASURE_Services_at_Start_of_Period = CALCULATE( DISTINCTCOUNT(crm[serviceid]), FILTER( ALLEXCEPT( crm, crm[groupname], LKP_group[Group], crm[productcategoryname], LKP_productcategory[Product Category], crm[customertypename], crm[disconnectiondate], LKP_calendar[Date] ), crm[activationdate] <= MAX(crm[activationdate]) ) ) And this is the DAX code for Running total, but shifting the date back by month (BY column) MEASURE_Services_at_Start_of_Period_Last_Month = VAR temp1 = CALCULATE( DISTINCTCOUNT(crm[serviceid]), DATEADD(LKP_calendar[Date], -1, MONTH)) VAR temp2 = CALCULATE( temp1, FILTER( ALLEXCEPT( crm, crm[groupname], LKP_ubgroup[UB Group], crm[productcategoryname], LKP_productcategory[Product Category], crm[customertypename], crm[disconnectiondate], LKP_calendar[Date] ), crm[activationdate] <= MAX(crm[ubactivationdate]) ) ) RETURN temp2 However, this is the outcome I received.
List of values with filter, kindly help me!
Hi Team, Kindly help me for filter visual. I have the 'Data' column and categories columns. like below snapshot format. we required filter, If I select categories col of 'a' then "a" related all data shown like below snapshot. If select 'b' then "b" related of all data required. How to it in DAX with this? Note:- In power query it is possible, by splitting muliple cols and we will do it. but have performacne impact, kindly help me to it using DAX?
Question on Relatedtable dax function usage
Please may I have help understand how the relatedtable function on the sales table is cutting down filters of Category and Subcategory for this average measure. In my understanding relatedtable fuction returns a related table. But need help understand how the related table helps surpass the category and subcategory filters This is my Model, I have two tables sales and product connected through Product key Sample Data Products Table Sales table
Filtering a table using TOP N slicer
Hi, i have a TOP N slicer which is a what if parameter from 0 to 50, i am trying to filter TOP N qty for a product in a table. please note there is already a TOPN filter on week for the table visual like pic below, so another topn is not possible and the data does not have an id/unique column and the products are repeted several times and the week in the sample is different from the topn loaddateweek. I am using a DAX measure to calculate the top products TOP N measure = VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN]) RETURN SWITCH(TRUE(), SelectedTop = 0, [Still to supply_sum], RANKX( ALLSELECTED(table[week],table[Product]), [Still to supply_sum], ,DESC,Dense ) <= SelectedTop, [Still to supply_sum] ) but this is not filtering for only 1 value when top n filter selected =1, but shows multiple value. Sample data is like below: Week Product Still to supply_sum rank 2024 34 6708 58720 1 2024 40 7706 64919 1 2024 38 6708 48120 2 2024 40 6708 43040 2 2024 38 731 34400 3 2024 36 6708 33560 3 2024 39 7448 36000 3 2024 41 6961 32040 5 2024 38 300 30080 6 2024 36 300 28120 7 2024 30 7551 24720 9 2024 41 122116010 22160 10 2024 36 731 21560 11 2024 41 7551 19790 15 2024 32 7551 19168 17 2024 31 300 16204 24 2024 32 300 15669 25 Not sure how to solve this issue to just have rank that does not repeat for a another row for the same product. Thanks for the help in advance.TOP N and Others in several subcategories
Hello guys, I have organizational hierarchy table, which contains 3 levels: path_5, path_6 and ziskatel. Then I have data table, where is id of lowest level of hierarchy (id_ziskatel). I have created meassure, which calculate cumulative sum of profit. 12M príjem zo štruktúry = VAR EndDate = MAX(tbl_kalendar[datum]) VAR StartDate = EDATE(EndDate,-12)+1 VAR Result = CALCULATE( SUM(data[prov]), DATESBETWEEN(tbl_kalendar[datum],StartDate,EndDate), data[id_prijem]=2) return Result Afterall, I want to display in matrix table visualization in Power BI TOP 10 subject according to "12M príjem zo štruktúry" and others summarize into subject "Others". I tried code: TOP N = VAR Table_TOP10_ziskatel = TOPN(10, ALLSELECTED(tbl_rebrik[ziskatel]), [12M príjem zo štruktúry]) VAR TOP10_ziskatel = CALCULATE( [12M príjem zo štruktúry], KEEPFILTERS(Table_TOP10_ziskatel)) VAR Others_ziskatel = CALCULATE( [12M príjem zo štruktúry], ALLSELECTED(tbl_rebrik[ziskatel])) - CALCULATE([12M príjem zo štruktúry], Table_TOP10_ziskatel) VAR Current_Product_ziskatel = SELECTEDVALUE(tbl_rebrik[ziskatel]) VAR Result_1 = IF(Current_Product_ziskatel<>"Ostatní",TOP10_ziskatel,Others_ziskatel) VAR Table_TOP10_path_6 = TOPN(10, ALLSELECTED(tbl_rebrik[path_6]), [12M príjem zo štruktúry]) VAR TOP10_path_6 = CALCULATE( [12M príjem zo štruktúry], KEEPFILTERS(Table_TOP10_path_6)) VAR Others_path_6 = CALCULATE( [12M príjem zo štruktúry], ALLSELECTED(tbl_rebrik[path_6])) - CALCULATE([12M príjem zo štruktúry], Table_TOP10_path_6) VAR Current_Product_path_6 = SELECTEDVALUE(tbl_rebrik[path_6]) VAR Result_2 = IF(Current_Product_path_6<>"Ostatní",TOP10_path_6,Others_path_6) VAR Table_TOP10_path_5 = TOPN(10, ALLSELECTED(tbl_rebrik[path_5]), [12M príjem zo štruktúry]) VAR TOP10_path_5 = CALCULATE( [12M príjem zo štruktúry], KEEPFILTERS(Table_TOP10_path_5)) VAR Others_path_5 = CALCULATE( [12M príjem zo štruktúry], ALLSELECTED(tbl_rebrik[path_5])) - CALCULATE([12M príjem zo štruktúry], Table_TOP10_path_5) VAR Current_Product_path_5 = SELECTEDVALUE(tbl_rebrik[path_5]) VAR Result_3 = IF(Current_Product_path_5<>"Ostatní",TOP10_path_5,Others_path_5) VAR Result = SWITCH( TRUE(), ISINSCOPE(tbl_rebrik[ziskatel]) && ISINSCOPE(tbl_rebrik[path_6]) && ISINSCOPE(tbl_rebrik[path_5]),Result_1, ISINSCOPE(tbl_rebrik[path_6]) && ISINSCOPE(tbl_rebrik[path_5]),Result_2, ISINSCOPE(tbl_rebrik[path_5]),Result_3) return Result But It works only for first level of hierarchy "path_5". If I drilldown to path_6, I will get just TOP 10 in path_6, but not the subject others ("Ostatní"). Can you please help me? Also I would like to rank those TOP 10 subjects according to amount descending and the others puts in the end. Thank you.Create new table from results of linestx dax
I have created a Linestx dax as - Revenue trend = VAR line = LINESTX ( ALLSELECTED('Sales order line fact'[Order month]), -- This respects the slicer [Total revenue trend], 'Sales order line fact'[Order month] ) VAR slope = SELECTCOLUMNS(line, [Slope1]) VAR intercept = SELECTCOLUMNS(line, [Intercept]) VAR x = SELECTEDVALUE('Sales order line fact'[Order month]) VAR y = x * slope + intercept RETURN y I want to create a temp table from the output that will look something like this
