Sorting X- axis based on Month-Year slicer selection
Hi Team, I am facing a chellenge to sort x-axis based on selection of filter. Image-1 Here L12M Amount is the last 12 months data from selected month. PL12M Amount is the previous last 12 months data from selected month. I want to sort the X- axis month based on selection of month in slicer, if I select Feb 2022, Feb should be at the rightmost position in place of December, remaining months need to be sorted accordingly. So, ideally if we read from right to left Feb to March at the left. Image 2 is the sorting order I want. Thank You, SSRk
Masure of The difference between individual months for 12 RM
Hello, I'd like to insert a column with calculated difference between the previous months and 'current' months. The difference should base on the values from 12 RM measure as indicated below. 12 RM Category rate = IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0), SUMX(VALUES('Calendar'[Month Yr]), [Pre 12 RM Cat rate]) , --To force the '0' and the division by 12 for the RollBack [Pre 12 RM Cat rate]) Pre 12 RM Cat rate = VAR RollBack_Table = CALCULATETABLE(FILTER(DATESINPERIOD('Calendar'[Dates], MAX('Calendar'[Dates]), - 12 , MONTH) , 'Calendar'[Dates] = EOMONTH('Calendar'[Dates],0)) , 'Calendar'[Flag_Projec]) VAR RollBack_Table_2 = CALCULATETABLE(FILTER(DATESINPERIOD('Calendar'[Dates], MAX('Calendar'[Dates]), - 11 , MONTH) , 'Calendar'[Dates] = EOMONTH('Calendar'[Dates],0)) , 'Calendar'[Flag_Projec]) VAR Cat_current = CALCULATE([MTD cat rate], 'Calendar'[Current Month]) RETURN IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0), SUMX(RollBack_Table , [MTD cat rate]) , SUMX(RollBack_Table_2,[MTD cat rate]) + Cat_current ) Do you have any ideas how could I cope with that? Thank you in advance! Mik
Repeat ID within a Rolling 12 month calculated Column
Hello All, Unfortunately, I am unable to share my data for security reasons. I am looking to create 2 columns depending on Category 1 or 2 equalling YES, it will tell me if the customer ID has appeared before within the last 12 months and if so how many times. an example of my data is below Row ID Date customer ID Category 1 Category 2 1 01/01/2022 1 YES NO 2 01/01/2022 2 YES NO 3 01/02/2022 1 NO YES 4 01/03/2022 1 YES YES any help would be great, THANKS ALLRolling Totals for the last month without Date context
I am trying to get the 12 month Rolling Total Amount to show in a table where I dont have the date in the row context I have State or city dimention as row context I just need the rolling totals for the last month i.e. Nov 2021 to Oct 2022 I need the sum of the total amounts for the last 12 months as one of the columns in the table below (column 6) irrespective of whatever date filters / slicers are picked can someone please help with this12 Month Rolling Average (Fiscal Calendar) Calculation
Hi, I'm trying to recreate the following formula from Excel in DAX: The 12 month rolling avergae formula is: =SUM(E81:E133)/SUM(D81:D133)*100000 =SUM(E82:E134)/SUM(D82:D134)*100000 =SUM(E83:E135)/SUM(D83:D135)*100000 Column E is Total X Column D is Total Y So it's basically summing the last 53 rows of the 'Total X' column divided by last 53 rows of 'Total Y' column * 100000. I'm using the company calendar which is fiscal periods so cannot use any of the inbuilt date/time intelligence functions or the quick measures. My calendar looks like this: Please could someone explain how I can create the same formula in dax...It's driving me mad!
Rolling 12 Month Headcount Sum as of Start of Year
Hi all, I am stuck once again! My goal is to create a Rolling 12 Month Headcount Sum measure as of the start of the year. For example, headcount as of 1/1/2021 was 2,690. I want to apply this to every month in 2021 and then sum for those 12 months resulting in a total of 32,280. To complicate this a little bit more here is another example. Since it is February 2022, I would want to calculate a sum of the last 12 months knowing that the start of year headcount was 2,690 in 2021 and is now 3,007 in 2022. The expected measure would sum 2 months of 2022 at 3,007 headcount and 10 months of 2021 at 2,690 for a total of 32,914. I hope this makes sense as I have a difficult time trying to describe this. Here is a table of my data: Year Month Start of Year Headcount Rolling 12 Month Headcount Sum Expected Result 2021 Jan 2,690 2021 Feb 2,690 2021 Mar 2,690 2021 Apr 2,690 2021 May 2,690 2021 Jun 2,690 2021 Jul 2,690 2021 Aug 2,690 2021 Sept 2,690 2021 Oct 2,690 2021 Nov 2,690 2021 Dec 2,690 2,690 32,280 2022 Jan 3,007 3,007 32,597 2022 Feb 3,007 3,007 32,914 Here is the DAX for Start of Year Headcount and Rolling 12 Month Headcount Sum: Start of Year Headcount = CALCULATE ( COUNTROWS ( Headcount ), STARTOFYEAR ( 'Date'[Date] ) ) Rolling 12 Month Headcount Sum = CALCULATE ( [Start of Year Headcount], DATESBETWEEN ( 'Date'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ), LASTDATE ( 'Date'[Date] ) ) ) Thanks in advance for your time and efforts! Please let me know if I can supply any additional information that may be helpful.Rolling sum in matrix categories with slicer
Hi, I'm quite new to DAX and Power BI and I'm having a struggle getting this rolling sum to work with category in matrix using a slicer in the report. We have a month slicer in the report and this sample is for 202111 selection. In matrix we want for each month to be shown rolling 12 month sum per areaid. I have managed to get the rolling sum to work without category, but it does not work with categories. Is this even possible with DAX? I know I could do this with other reporting tools so I would assume this should be possible with DAX too but I just can't figure it out. Here is the DAX I have now. ActionsRolling:= VAR endDate = MAX(Calendar[YYYYMM]) VAR startDate = endDate - 100 VAR RollingSum = CALCULATE(SUM(Actions[checkins]),FILTER(ALL(Actions), AND(Actions[YYYYMM] > startDate, Actions[YYYYMM] <= endDate))) RETURN RollingSum Actions_R12:= VAR endDate = MAX(TimeSlicer[YYYYMM]) VAR startDate = endDate - 100 VAR MeasureR = Actions[ActionsRolling] RETURN IF(MIN( Calendar[YYYYMM]) <= startDate, BLANK(), IF(MIN(Calendar[YYYYMM]) > endDate, BLANK(), MeasureR)) With this DAX we get the result like this. These numbers in matrix tables might not be 100% accurate as I quickly put them up in excel but you should get the idea of the problem here. 202012 202101 202102 202103 202104 202105 202106 202107 202108 202109 202110 202111 Area1 141 158 160 167 175 178 182 187 191 190 180 160 Area2 141 158 160 167 175 178 182 187 191 190 180 160 Area3 141 158 160 167 175 178 182 187 191 190 180 160 total 141 158 160 167 175 178 182 187 191 190 180 160 While the result should look like: 202012 202101 202102 202103 202104 202105 202106 202107 202108 202109 202110 202111 Area1 11 10 9 10 12 12 13 14 15 15 15 15 Area2 116 135 140 147 153 159 163 168 171 173 163 144 Area3 14 13 11 10 10 7 6 5 5 2 2 1 total 141 158 160 167 175 178 182 187 191 190 180 160 Here are the tables with joins. Thank you in advance! - AkuAdvanced DAX help - Rolling 12 month average of another measure
Hi Guys, I am hoping a DAX guru can help with a complicated DAX problem that I have. I initially started using the rolling 12 month template provided by the sqlbi guys (https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/) but had to deviate substantially due to the differences in the underlying data and additional requirements around visualisation filters. The goal Goal is to produce a rolling 12 month average of an 'average transaction value per customer' per account_id Complications Unfortunately the data is structured like this: Account_ID System All Flows ($) Net Membership Year_Month 1 A 1,000,000 Sep 21 1 S 50,000 Sep 21 1 null 5000 Sep 21 2 A 30,000 Sep 21 2 S 5,000 Sep 21 2 null 300 Sep 21 Measure 1 - Total $ spent - sum(All Flows) Measure 2 - Total Customers - sum(Net Membership) Measure 3 - divide( [Total $ spent], [Total Customers]) What I need is to have an average of measure 3 over 12 months per account ID Visualisation requirements The default view is all companies. However users can click on individiual companies to filter only for that companies' details. The user can also multi select different companies (i.e. select company 2, 6, 😎 and the measure needs to calculate correctly. There are a myriad of other filters based on location, sales manager for each company which isn't shown above but are referrenced in other dimension tables. Other requirements I could probably 'cheat' by creating a table that pre-calculates the value for each employer. However due to interactions with other measures, this isn't preferred. Also to de-clutter the PBI file (and also challenge my DAX understanding) I would like to do all this within a measure. Where i got to so far All flows per Mbr Rolling 12m = VAR LastCurrentDate = LASTDATE('f Combined Facts'[YEAR_MONTH]) var LastYearDate = DATEADD(LASTDATE('f Combined Facts'[YEAR_MONTH]),-1,YEAR) var HasOneID = HASONEVALUE('f Combined Facts'[ACCOUNT_ID]) var tbl = SUMMARIZE( filter('f Combined Facts', 'f Combined Facts'[YEAR_MONTH] > LastYearDate && 'f Combined Facts'[YEAR_MONTH] <= LastCurrentDate), 'd Account Managed Employers'[ACCOUNT_ID], 'f Combined Facts'[YEAR_MONTH], "All Flows", sum('f Combined Facts'[All flows]), "Net Mbrs", sum('f Combined Facts'[Net Membership (Agg)]), "AvgFlow", if( ISBLANK( divide(sum('f Combined Facts'[All flows]),sum('f Combined Facts'[Net Membership (Agg)])) ),0, divide(sum('f Combined Facts'[All flows]),sum('f Combined Facts'[Net Membership (Agg)]))) ) VAR SingleEmployer = calculate(sumx(tbl, [AvgFlow])) var AllEmpnum = sumx(tbl, [All Flows]) var AllEmpD = sumx(tbl,[Net Mbrs]) var Result = if(HasOneID,SingleEmployer,divide(AllEmpnum,AllEmpD)) RETURN Result What I'm struggling to do The variable table seems to be calculated/created correctly on the month level, but i'm struggling to 'take a step back' and aggregate at a rolling 12 month level for this calculation. The initial sqlbi help article has the monthly values 'materialised' and then uses datesinperiod to filter down to the relevant 12 month period. I'm not sure how this can be done within a single measure. Am grateful for any guidance. Many thanks
