This is my personal collection of commonly used DAX formulas—ones I refer back to constantly while building reports and dashboards. Over time, I’ve refined these formulas into variations that work efficiently across different datasets. Many of these serve as templates, allowing me to quickly apply core logic and adjust where needed.

I often use macros and hotkeys to insert the skeleton of some of these formulas, making it easier to standardize my approach across projects. While some of these may seem repetitive, they have slight variations that fit different contexts.

I update this list about once a month, keeping only the formulas I find myself using over and over. Some formulas may lack full context here, but they are part of my workflow for quick implementation and problem-solving in Power BI. This is essentially my own evolving DAX reference library, built from real-world usage. 🚀

____________________________________________________________________________________________________________________

Basic Calculations

• Absolute Value Calculation
  
  

  
                      ABSMeasure = ABS('Dataset'[MetricValue])
                              

  Returns the absolute value of a specified numeric column to eliminate negative values.
  
  

Goal Tracking and Thresholds

• Goal-Based Threshold
  
  

  
                      DynamicTarget = 
                      VAR CurrentDate = MAX('Dataset'[EntryDate])
                      RETURN
                          SWITCH(
                              TRUE(),
                              CurrentDate >= DATE(2024, 5, 1), 125,
                              CurrentDate >= DATE(2024, 3, 1), 187.5,
                              CurrentDate >= DATE(2024, 1, 1), 250,
                              BLANK()
                          )
                              

  Adjusts a goal dynamically based on date ranges, switching values at key checkpoints.
  
  
• Conversion-Based Goal
  
  

  
                      ScalingFactorGoal = 
                      VAR ConversionFactor = 400
                      RETURN
                          SWITCH(
                              TRUE(),
                              'Dataset'[EntryDate] <= DATE(2024, 3, 1), 250 * ConversionFactor,
                              'Dataset'[EntryDate] <= DATE(2024, 5, 1), 225 * ConversionFactor,
                              200 * ConversionFactor
                          )
                              

  Adjusts goal targets using a multiplier and date-based conditions.
  
  

Date and Time Formatting

• Weekly Summary Display
  
  

  
                      ShowWeeklySum = 
                      VAR CurrentWeek = MAX('Dataset'[WeekIndex])
                      VAR LastDayOfWeek = 
                          MAXX(
                              FILTER(
                                  'Dataset',
                                  'Dataset'[WeekIndex] = CurrentWeek
                              ),
                              'Dataset'[EntryDate]
                          )
                      RETURN
                      IF(
                          MAX('Dataset'[EntryDate]) = LastDayOfWeek,
                          [WeeklySum],
                          BLANK()
                      )
                              

  Displays the total for the current week only on the last recorded day of that week.
  
  
• Formatted Date Output
  
  

  
                      PrettyDate = 
                      VAR DayNumber = DAY('Dataset'[EntryDate])
                      VAR Suffix = 
                          SWITCH(
                              TRUE(),
                              DayNumber IN {1, 21, 31}, "st",
                              DayNumber IN {2, 22}, "nd",
                              DayNumber IN {3, 23}, "rd",
                              "th"
                          )
                      RETURN
                          FORMAT('Dataset'[EntryDate], "mmm ") & DayNumber & Suffix
                              

  Converts a date into a formatted string with month abbreviation and ordinal suffix (e.g., "Jan 3rd").
  
  

Data Summarization and Tracking

• Work Week Numbering
  
  

  
                      WeekIndex = 
                      VAR CurrentDate = 'Dataset'[EntryDate]
                      VAR RunningTotal = 
                          CALCULATE(
                              SUM('Dataset'[IsNewWeek]),
                              FILTER(
                                  'Dataset',
                                  'Dataset'[EntryDate] <= CurrentDate
                              )
                          )
                      RETURN RunningTotal
                              

  Assigns a unique number to each work week based on sequential counting.
  
  
• Resolved Requests Count
  
  

  
                      ResolvedEntries = 
                      CALCULATE(
                          COUNTROWS(Dataset_Requests),  
                          NOT(ISBLANK(Dataset_Requests[CheckedDate])) &&  
                          NOT(ISBLANK(Dataset_Requests[CompletionDate])) &&  
                          WEEKDAY(Dataset_Requests[CompletionDate], 2) <= 5 &&  
                          DATE(YEAR(Dataset_Requests[CompletionDate]), MONTH(Dataset_Requests[CompletionDate]), DAY(Dataset_Requests[CompletionDate])) = MAX(DateTable[Date])  
                      )
                              

  Counts the number of resolved entries while ensuring valid request and completion dates.
  
  
• Average Completion Time
  
  

  
                      CompletionRate = 
                      VAR Last30DaysData = 
                          FILTER(
                              Dataset_Requests,
                              Dataset_Requests[CheckedDate] >= (TODAY() - 30) &&  
                              Dataset_Requests[CheckedDate] <= TODAY() &&  
                              NOT(ISBLANK(Dataset_Requests[CheckedDate])) &&  
                              NOT(ISBLANK(Dataset_Requests[CompletionDate]))  
                          )
                      VAR TotalTime = 
                          SUMX(
                              Last30DaysData,
                              DATEDIFF(Dataset_Requests[CheckedDate], Dataset_Requests[CompletionDate], DAY)
                          )
                      VAR TotalCompleted = 
                          COUNTROWS(Last30DaysData)
                      VAR AverageCompletion = IF(TotalCompleted > 0, DIVIDE(TotalTime, TotalCompleted, 0), BLANK())
                      RETURN 
                      "Average Completion Rate (Last 30 Days): " & FORMAT(ROUND(AverageCompletion, 2), "0.00")
                              

  Calculates the average resolution time (in days) for completed tasks over the last 30 days.
  
  
• Time to Completion Breakdown
  
  

  
                      TTCAverageFormatted = 
                      VAR AvgMinutes = [TTCAverage (Minutes)]
                      VAR Days = INT(AvgMinutes / 1440)  
                      VAR Hours = INT(MOD(AvgMinutes, 1440) / 60)  
                      VAR Minutes = MOD(AvgMinutes, 60)  
                      RETURN
                      FORMAT(Days, "0") & "d " & FORMAT(Hours, "0") & "h " & FORMAT(Minutes, "0") & "m"
                              

  Converts a time duration (in minutes) into a readable format (e.g., "2d 5h 30m").