Date Table 12 week period
I have a Date Table which in the 12 Week Period Column creates a new period for every 12 weeks as per the column highlighted. What the 12 Weeks Period is doing through is going back to 1 at the beginning of the new year so for Date 01 January 2024 the 12 Weeks Period has gone back to 1 but there are not 12 weeks in Period 5. How do I get the column so it just rolls on without returning back to 1 for a new year? danextian Anonymous Anonymous tamerj1Measure for colouring columns on a higher aggregation level than detailed data
Hi there I kindly request your help/hints on the follwing, because I am stuck somwhere in between... I need to color a column chart based on the average out of X Months, whereas No of months is from a relative date slicer The data are weekly based (Date of Sunday) and is counting number of contracts: MyMeasure = Sum( MyTable[NoOfContracts] ) Using my datetable I am visualising MyMeasure in a columnchart I wrote a measure to get the average per No of selected Months: MyMonthlyAverage = AVERAGEX ( VALUES( calendar[MonthYear]), MyMeasure ) Based on the relation between MyMeasure and MyMonthlyAverage I want to decide the color of the column, "Geen" for above, "Red" for below MyMonthlyAverage. Unfortunateley it seems that the number per Month (MyMeasure) is not divided by no of months but by the number of Sunday-dates in the selected period (e.g. MyMonthlyNoOfContracts / No of Sunday-Dates (52) and not MyMonthlyNoOfContracts / No of Months selected (12)) MyColumnColor = var check = [MyMeasure] var limit = [MyMonthlyAverage] return SWITCH( TRUE(), check < limit, "Red", "Green") Aways "Green" gets returned and so are the columns Question: What is the Syntax to write a correct and proper MyMonthlyNumberOfContracts? Appreciating your Input thx in advance Thomas
Issue with SELECTEDVALUE function when used with CALCULATETABLE in DAX
Hello, I am currently working on a report where I've dedicated a page to individual companies. Users navigate to this page via "drill through" from another page. On this particular page, I would like to see other companies that are within the same industry as the company in question. With that aim, I have constructed the following DAX formula: SummarizeTable = VAR CurrentIndustry = SELECTEDVALUE('Account Meta Data'[Industry]) VAR CompanyList = CALCULATETABLE( VALUES('Account Meta Data'[DatabaseName]), 'Account Meta Data'[Industry] = CurrentIndustry ) Return CompanyList However, the CompanyList produced doesn't align with my expectation. It seems to comprise of companies which lack industry definitions in my Company Meta Table. Although I have tested the CurrentIndustry = SELECTEDVALUE('Account Meta Data'[Industry]) measure separately and confirmed it produces the correct value, it doesn't seem to function as intended within the CALCULATETABLE function. When I replace CurrentIndustry with a specific industry value (for example, "Agriculture"), the list is generated correctly. Could someone kindly help me understand what might be wrong? Any assistance would be greatly appreciated! Best regards, Brenda
Recursive relationships
Let's say we have a simple table for entities and their IDs (TableA) and another table that defines relationships in a format similar to this: IncomingID OutgoingID <some attributes that are irrelevant> 1 2 2 3 2 4 5 6 5 7 We need to be able to find all entities related to any given "starting" ID; in the sample data above, for ID=1, we would expect {2, 3, 4} as the correct set, as 2 is related to 1 and then both 3 and 4 are related to 2. Relationship defined that way can be recursive. What is the correct approach to configure this as a part of the model and what kind of DAX code can be used to achieve the sample scenario?Bar chart: Avoiding bars without value with an condition!
Hello kind people, I am creating a bar chart which represents the number of visitors per week. I want to keep those bars with 0 visitor in the chart, but only in those weeks after the original week when they have visited! Please see the graph below what I mean: I tried with following DAX dreaming to fix the issue, but not working. Power BI had error message that "the search text provided to function "find" could not be found in the given text" even though I didn't even use Find function :O! Visitors = VAR FirstVisitDate = CALCULATE ( MIN ( 'Usage_log'[timestamp].[Date] ), 'Usage_log' ) VAR WeekDate = SELECTEDVALUE( DateTable[StartDate] ) VAR visitor = CALCULATE ( DISTINCTCOUNT(Usage_log[user_Id]), ALL(Usage_log), KEEPFILTERS(ALL('User_Categorization_Revised'[User_Type])) ) RETURN IF ( WeekDate < FirstVisitDate, BLANK(), IF(ISBLANK(visitor) || visitor = 0, 0, visitor) ) Thanks a lot in advance if you could help ❤️
