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.

Comments

Popular posts from this blog

Diploma In Data Analytics with Excel and Power BI

I will Be teaching the below mentioned course in association with Sanyukta Trust Sanyukta Trust is proud to introduce a special course designed to transform you into Data Analytics Expert! In this course, you will benefit by learning how to use MS Excel and Power Bi (Business Intelligence) to read, clean, transform, visualize and analyze the data.  Join our AIBTE (All India Board of Technical Education) Government recognised Online course of Diploma in Data Analytics with Microsoft Excel and Power Business Intelligence (Bi) Session: 16 sessions plus assignment submissions  Course Highlights: - Introduction to Data Analytics & MS Office Tools - In depth knowledge of MS Excel & Power Bi - Clean and Transform data with Power Query - Data Modelling with Dax/Power Pivot  - 3D maps  - Interactive Charts and Data Visualization with Slicers - Interactive Dashboards Days: Twice a Week - Saturday - 5pm to 6.30pm - Sunday - 11am to 12.30pm ...

Goal Seek feature in Excel

Have you ever found yourself plugging in successively higher and lower values into an input cell, hoping to arrive at a certain answer? A tool that is built in to Excel does exactly this set of steps. Select the cell with the Payment formula. On the Data tab, in the Data Tools group, look for the What-If Analysis dropdown and choose Goal Seek…. The figure below shows how you can try to set the payment in B5 to $425 by changing cell B1 Goal Seek finds the correct answer within a second. Note that the formula in B5 stays intact. The only thing that changes is the input value typed in B1. Also, with Goal Seek, you are free to experiment with changing other input cells. You can still get the $425 loan payment and the $25,995 car if your banker will offer you a 71.3379-month loan!