DAX formula for Monthly Cumulative Backlog Ticket Calculation
Hello, i have found a response in regards of the calculation of Monthly backlog and I m trying to implement a similar measure on my Power BI reports and i think the method described of solving the problem is close to the one i m looking for, but in my attempt to download the pbi file and see the calculation, the file is missing. https://www.linkedin.com/safety/go?url=https%3A%2F%2Fcommunity.fabric.microsoft.com%2Ft5%2FDAX-Commands-and-Tips%2FNeed-help-to-create-DAX-formula-for-monthly-backlog-ticket%2Ftd-p%2F1651032&trk=flagship-messaging-web&messageThreadUrn=urn%3Ali%3AmessagingThread%3A2-OTkwNjEwMTYtOTMwNS00MzJkLThmNDQtMjMzNjUwNTFjYTEzXzAxMw%3D%3D&lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base%3BlGvr8tgdR92f5F9Hpj%2BQRg%3D%3D Futhermore some more information on what i need to achieve and how the solution you provided is close but needs something more to be completed. The Final Output of the report i m trying to implement is the following. The Backlog calculation should provide a cummulative sum of the tickets that remain open (Status = Open) and be added on the following month. For example: On September 2023 Open Tickets (45) - Closed Tickets (23) + Backlog (1) = 23. On your previous response regarding the backlog calculation, i notice that the dax formula doesnt take in consideration the previous backlog tickets if on the previous month none was opened or complete. Year Month Opened_Tickets Closed_Tickets Backlog 2023 June 1 0 1 2023 July 3 3 1 2023 August 0 0 1 2023 September 45 23 23 2023 October 68 51 40 2023 November 72 38 74 2023 December 59 36 97 2024 January 127 69 155 2024 February 135 64 226 2024 March 104 105 225 2024 April 216 120 321 2024 May 138 237 222 2024 June 5 5 222 A few things on my raw data and tables that i need to do the calculation on. Table 1 = Jira Raw that contains the following Columns: Key Status Created Resolved Τckt-1043 Open 3/6/2024 16:41 Τckt-1042 Open 3/6/2024 14:30 5/6/2024 14:30 Τckt-1041 Open 3/6/2024 12:43 My calendar Table is called Calendar [Date] that contains year,quarter,month, week and weekday. My relationships look as follows : Active relationship between Calendar [Date] and Jira raw [created] Inactive relatonship between Calendar [Date] and Jira raw [Resolved] As far as my calculations : Opened_Tickets = if(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))), 0, CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Created]))) Closed_Tickets = IF(ISBLANK(CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))),0,CALCULATE(COUNT(Jira_Raw[Key]), USERELATIONSHIP('Calendar'[Date],Jira_Raw[Resolved]))) Backlog = CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Created] <= max('Calendar'[Date]) || not(ISBLANK(Jira_Raw[Created])))) - CALCULATE(DISTINCTCOUNT(Jira_Raw[Key]), FILTER(Jira_Raw , Jira_Raw[Resolved] <= max('Calendar'[Date]) && not(ISBLANK(Jira_Raw[Resolved])))) Last but not least i m new to the whole dax and power BI world so any help on achieving the correct calculation of the backlog should be much appreciated. Best Regards.
SUMX giving zero total for a column in Table visual
I am looking at a table visual where the line totals are correct but the Grand Total is coming back as zero despite using SUMX The measure is as follows PreviousMonthValue = VAR _currentOpp = SELECTEDVALUE(Opportunities_ME[OpportunityNumber]) VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM]) VAR _prevMonthEnd = EOMONTH(_currentDate, -1) VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) ) VAR _ValueLM = CALCULATE( SUMX( FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ),Opportunities_ME[EstimatedValueBase]) )+0 RETURN _ValueLM Anyone know why?YTD YOY measure without a date table
Hi! I have a matrix viz in my sales report that lists the sales years and months for our teams' vendors. One of the asks is to 1) filter the total sales for 2024 and 2023 for YTD based on the month, and 2) create YOY and YOY% columns based on YTD. I know how to do this with PBI's time intelligence functions, but the issue is the data comes only as months and years so there's no date columns. I tried to first make a calculated column finding the max month for 2025, then using that in the YOY measure to either look at 2024 or 2023 depending on what sales year you're lookign at. But it's only returning the actual sales amounts. any insight is appreciated - I would prefer this to be dynamic for any years. YTD = var maxmonth = MONTH(TODAY()) RETURN IF('Total Vendor'[MonthNum] <= maxmonth, "Y", "N") YTD YOY = var curryear = 2025 var ty = CALCULATE(SUM('Total Vendor'[sales]), 'Total Vendor'[YTD]="Y") var ly = IF(MAX('Total Vendor'[Year])=curryear, CALCULATE(SUM('Total Vendor'[sales]), 'Total Vendor'[Year] = 2024,'Total Vendor'[YTD]="Y"), CALCULATE(SUM('Total Vendor'[sales]), 'Total Vendor'[Year] = 2023,'Total Vendor'[YTD]="Y")) return ty-lyVary Chart date range
Hello I want to dynamically change the date range on a chart, depending on a selected RepPeriod in the following table Using the following slicer I’m testing using 2 measures Measure #1 VAR _RepPeriod = SELECTEDVALUE ( ReportingPeriods[RepPeriod] ) VAR _RepDate = CALCULATE ( MAX ( ReportingPeriods[RepDate] ), ReportingPeriods[RepPeriod] = _RepPeriod) RETURN _RepDate Measure #1 returns the correct date ( _RepDate ) Measure #2 VAR _RepPeriod = SELECTEDVALUE ( ReportingPeriods[RepPeriod] ) VAR _RepDate = CALCULATE ( MAX ( ReportingPeriods[RepDate] ), ReportingPeriods[RepPeriod] = _RepPeriod) RETURN IF( max('Date'[Date]) >= _RepDate && max('Date'[Date]) <= Today(), 1, 0 ) Measure #2 only returns 0 from the IF statement . max('Date'[Date]), is correct (the Date table is fully populated). I use it in the other measures. If I hard code a date into the IF, e.g. IF( max('Date'[Date]) >= date(2024,4,1) && max('Date'[Date]) <= Today(), 1, 0 ) it works as expected. I can’t understand why #Measure 2 is just returning 0 and I can’t vary the dates being passed into the IF statement???? Any suggestion, much appreciated… Thanks in advance Roy
Help with Measures that Counts How Many Projects Are Starting in Various Time Frames
Hi, I'm hoping someone here can help me fix some measures I've made. I have set up four measures to count how many construction projects start in various time frames: 30, 60, 90, and 120 days. The measure is written exactly the same way for all 4, with just variance in the dates. I am getting a count returned, but it is not correct. The screenshot below shows the Construction Start Dates, and also the first measure. The measure is intended to count how many instances of the task name "mobilization" occur between the specified date range. As you can see, between today and today +30 days (so, before ~9/5/2024) there are 2 projects scheduled to start, but the measure returns a count of 1. For Construction Starts between 30 and 60 days (~9/6 to ~10/6) which is correct. For Construction Starts between 60 and 90 days (~10/7 to ~11/6), it counts 2, which is correct. For Construction Starts between 90-120 days (~11/8 to ~12/9), it counts 1, but there are 2 projects scheduled to start in that time period. Here is the syntax I've used for each of the measures. I'm pretty sure this is a syntax issue, but haven't been able to figure out what I'm doing wrong. Any help is appreciated! ConStart+30 = IF(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()&&[Start]<=TODAY()+30))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()&&[Start]<=TODAY()+30))) ConStart+60 = IF(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+30 && [Start]<=TODAY()+60))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+31 && [Start]<=TODAY()+60))) ConStart+90 = if(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+61 && [Start] <=TODAY()+90))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+61 && [Start] <=TODAY()+90))) ConStart+90 = if(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+61 && [Start] <=TODAY()+90))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+61 && [Start] <=TODAY()+90))) ConStart+120 = if(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+91 && [Start] <=TODAY()+120))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+91 && [Start] <=TODAY()+120))) Thank you for looking and trying to help me! Kirstin
Slow DAX formula to calculate # Orders Delayed with adjustment
Hi I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters. The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM. The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM. The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør': Here is the DAX formula: # Ordre Forsinket = VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 ) VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 ) RETURN CALCULATE( DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), FILTER( ADDCOLUMNS( FILTER( 'Ordrelinjer', 'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0 ), "JusteretAfvigelse", 'Ordrelinjer'[Afvigelse i dage] - IF( RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"}, _LeveringstidRegionslagerRabat, _LeveringstidOevrigeRabat ) ), [JusteretAfvigelse] > 0 ) ) The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. Can I write the DAX formula in a more efficient, clean and performance optimized way? Best regards MortenCombining Measures and Spreading Proportional Values based on % of whole
I have a group of costs that are allocated throughout 6 areas, however some of those costs are not allocated to any area at all. I want to distribute the unallocated costs proportionally between the 5 or 6 areas based on each areas overall total divided by the sum total of all 6 areas. I created a measure to determine the % allocation. %JCIDBilledAmt to OfficeName = VAR _TotalJCIDBilledAmt = CALCULATE( [BilledAmt], All(Offices[OfficeName]) ) RETURN DIVIDE( [BilledAmt], _TotalJCIDBilledAmt) Now… What I want to do is take the $740,992 and multiply it by the 24.67%,… so on, so that amount is spread proportionally over each of the OfficeName. I ran this formula but it’s not giving me the amount for each area and only giving me a sum total. GLDTAmount for <Filter> Part3 Null x %JCIDBilledAmt to OfficeName = [GLDTAmount for <Filter> Part3 Null] * [%JCIDBilledAmt to OfficeName] How do I get that $740,992 spread proportionally? What I also need to do is add the SumAmount to the spread proportioned amount together. Any help or insight would be appreciated.
