Week over week measure
Hi, I have created this "# Open tickets EOP" measure and it is working and looks like this below: OpenTasksWoW = VAR MinDate = MIN ( 'dim_date'[Date] ) VAR MaxDate = MAX ( 'dim_date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( CurrentRow_tasks ), CurrentRow_tasks[taskCreatedDateDK] <= MaxDate, OR( CurrentRow_tasks[taskClosedDateDK] > MinDate, ISBLANK(CurrentRow_tasks[taskClosedDateDK]) ), REMOVEFILTERS ( 'dim_date' ) ) RETURN Result # Open tickets EOP = CALCULATE ( [OpenTasksWoW], LASTDATE ( 'dim_date'[Date] ) ) But now I also want to calculate the days the ticket is open week over week. This measure is not working because it shows only a value for the first week a ticket is open. DaysOpen = VAR CreatedDate = MAX(CurrentRow_tasks[taskCreatedDateDK]) VAR ClosedDate = MAX(CurrentRow_tasks[taskClosedDateDK]) VAR LastDateInPeriod = LASTDATE('dim_date'[Date]) RETURN IF( ISBLANK(ClosedDate), DATEDIFF(CreatedDate, LastDateInPeriod, DAY), IF( ClosedDate <= LastDateInPeriod, DATEDIFF(CreatedDate, ClosedDate, DAY), DATEDIFF(CreatedDate, LastDateInPeriod, DAY) ) ) I want a value for EVERY week a ticket is open. So for every end of period it should calculate the days it is open. In this example: INCIDENT798633 should have value 11 for Year_Week_Numeric = 202506, value 18 for Year_Week_Numeric = 202507, value 25 for Year_Week_Numeric = 202508, etc.Aircraft Circle Indicator measure not working in Shape Visual
Hi All, I had a question with Aircraft circle Indicator measure Test Color Ind = VAR v1 = SELECTEDVALUE(ccc[type display]) VAR v2 = SELECTEDVALUE(ccc[contract status]) VAR L12M = TODAY() - 365 VAR L24M = TODAY() - 730 VAR CD = SELECTEDVALUE(ccc[changedate]) VAR CID = SELECTEDVALUE(ccc[contractid]) VAR IsCC = IF(CID IN VALUES(Aircraft[contractid]), "current", "previous") RETURN IF ( IsCC = "current", SWITCH (TRUE(), v2 = "Terminated", "#000000", v2 = "Suspended", "#FF0000", v2 = "Active" && v1 = "Suspended" && CD <= TODAY() && CD >= L12M, "#FF0000", v2 = "Active" && v1 = "Suspended" && CD <= L12M && CD >= L24M, "#FFBF00", "#00FF00" )) For this measure, colour in Shape Visual is not picking correctly because in ccc table there is more than one record for each aircraft like the data of rows are: Date 19 Jan 2024, cs is Active, td is Enrollment Date 9 Jan 2024, cs is Active, td is Suspended Date 28 June 2023, cs is Active, td is Enrollment Date 14 June 2023, cs is Active, td is Enrollment Because of this kind of data of rows in Shape Visual it is not picking. Filter on this page I have Aircraft serial number(from aircraft table) to filter and check the logic for each aircraft. Now based on this challenge and situation Can anyone suggest whether is this possible to achieve with the change in the measure. Thank you in advance
Calculate Cost price based on the sales order
I have different ERP system to maintain cost price and CRM for sales order. I am bringing different source system into my azure Data warehouse (one source of truth), when I created a data model with Dim salesorder, Fact Sales order (Which contains details of sales happened, sales order id, gross sales ,discount , calendar id etc) whereas Fact Production data contains sales order id, cost price , calendar id. I also have DIm calendar which contains time stamps, calendar id that connects all table and sales order id that connects dim and facts. The problem is I am trying to get the cost price for the particular sales order id, where the machine was built in 2021 and sold in 2022. I am getting the cost price of only when the product is sold and not the cost price of when the machine started to built. I tried DAX measure to remove the filter to calulate cost price, it works but the cost price remains same for all years irrespective what I select in Calendar. I want to know how to calculate the cost price totally for the particular sales order id and once the sale is over in 2022 , it should not appear in 2023. Look the image below (Machine built in 2021-2022 and sold in 2022) How can I make the right dax measure? Total Cost Price All Periods = CALCULATE( SUM('Fact Production'[Basic Currency Amount Total]), ALLSELECTED('Dim Calendar') )
