Skip to main content

20 Time Intelligence Dax Measures

20 Time Intelligence DAX measures in Power BI with examples:

  1. Year-to-Date Sales:

    css
    YTD Sales = TOTALYTD([Total Sales], Calendar[Date])

  2. Month-to-Date Sales:

    css
    MTD Sales = TOTALMTD([Total Sales], Calendar[Date])

  3. Quarter-to-Date Sales:

    css
    QTD Sales = TOTALQTD([Total Sales], Calendar[Date])

  4. Previous Year Sales:

    mathematica
    Previous Year Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))

  5. Year-over-Year Growth:

    css
    YoY Growth = DIVIDE([Total Sales] - [Previous Year Sales], [Previous Year Sales])

  6. Rolling 3-Month Average Sales:

    sql
    3M Rolling Avg Sales = AVERAGEX(DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -3, MONTH), [Total Sales])

  7. Cumulative Sales:

    scss
    Cumulative Sales = SUMX(FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])), [Total Sales])

  8. Running Total Sales:

    scss
    Running Total Sales = SUMX(FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])), [Total Sales])

  9. Year-to-Date Profit:

    css
    YTD Profit = TOTALYTD([Total Profit], Calendar[Date])

  10. Month-to-Date Profit:

    css
    MTD Profit = TOTALMTD([Total Profit], Calendar[Date])

  11. Quarter-to-Date Profit:

    css
    QTD Profit = TOTALQTD([Total Profit], Calendar[Date])

  12. Rolling 12-Month Total Sales:

    scss
    12M Rolling Sales = SUMX(DATESYTD(Calendar[Date]), [Total Sales])

  13. Average Sales per Day:

    sql
    Avg Sales per Day = DIVIDE([Total Sales], COUNTROWS(DATESYTD(Calendar[Date])))

  14. First Date of Sales:

    mathematica
    First Sales Date = MINX(FILTER(Calendar, [Total Sales] > 0), Calendar[Date])

  15. Last Date of Sales:

    mathematica
    Last Sales Date = MAXX(FILTER(Calendar, [Total Sales] > 0), Calendar[Date])

  16. Number of Days with Sales:

    css
    Days with Sales = COUNTROWS(FILTER(Calendar, [Total Sales] > 0))

  17. Average Sales Growth Rate:

    scss
    Avg Sales Growth Rate = AVERAGEX(VALUES(Calendar[Year]), [YoY Growth])

  18. Total Sales for Selected Period:

    sql
    Sales for Selected Period = CALCULATE([Total Sales], DATESBETWEEN(Calendar[Date], [Start Date], [End Date]))

  19. Max Sales Date:

    scss
    Max Sales Date = CALCULATE(MAX(Calendar[Date]), FILTER(ALL(Calendar), [Total Sales] = [Max Sales]))

  20. Min Sales Date:

    scss
    Min Sales Date = CALCULATE(MIN(Calendar[Date]), FILTER(ALL(Calendar), [Total Sales] = [Min Sales]))

These time intelligence measures help you analyze data over different time periods, calculate growth rates, and track trends within your Power BI reports. Remember to adjust them based on your specific date and data structure.

Labels:

Comments

Post a Comment

Popular posts from this blog

Stockhistory Function

We begin with a list of stock ticker symbols and their respective company names. Our objective is to display the monthly close stock price from a user-defined date to the present.  We also want to display an in-cell line chart that visualizes the monthly price changes while identifying the monthly high and low over the requested time. The  STOCKHISTORY  function retrieves historical data about a financial instrument and loads it as an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriately sized array range when you press  ENTER . The syntax for the STOCKHISTORY function is as follows ( optional arguments are in square brackets ): =STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5]) stock  – Enter a ticker symbol in double quotes ( g.,  “MSFT” ) or a reference to a cell containing the Stocks data...
Post a Comment
Read more

Indirect Function

INDIRECT  is pretty cool for grabbing a value from a cell. Can  INDIRECT  point to a multi-cell range and be used in a  VLOOKUP  or  SUMIF  function?  You can build an  INDIRECT  function that points to a range. The range might be used as the lookup table in a  VLOOKUP  or as a range in  SUMIF  or  COUNTIF . In  Figure , the formula pulls data from the worksheets specified in row 4. The second argument in the  SUMIF  function looks for records that match a certain date from column A. Note:  Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is: =SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300")) Summary:  You can use  INDIRECT  to grab data from a multi-cell range.
Post a Comment
Read more

Data Analysis Tool Pack

  The  Analysis ToolPak  is an  Excel add-in  program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click on OK. 4. On the Data tab, in the Analysis group, you can now click on  Data Analysis . The following dialog box below appears. 5. For example, select Histogram and click OK to create a Histogram in Excel. Example Rank and Percentile The Rank and Percentile contained within the Analysis-ToolPak can be quickly used to find the rank of all the values in a list. The advantage of using the Rank and Percentile feature is that the percentile is also added to the output table. The percentile is a percentage that indicates the proportion of the list which is below a given number. Highlight the list (or the cells) which...
Post a Comment
Read more