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

Pivot Tables and how to use them

Excel pivot tables are very useful and powerful feature of MS Excel. They are be used  to create instant summaries, reports and data analysis from your raw data .  In this page, learn all about how to create an Excel pivot table and customize it. 1. What are Excel Pivot Tables  A pivot table  turns  your data into report format. Here is a sample Pivot table from sales data, showing  total sales by region. 2. How to Create a Pivot Table We will use 2019 sales data of a fictional company. This data contains 466 rows of sales information in columns – Month, Salesman, Region, Product, No.  Customers, Net Sales, Profit / Loss. Here is a preview of our data. To create a pivot table showing  totals sales by region , follow these steps. Select any cell in the data. Go to Insert ribbon and click “Pivot Table” button.  Click ok on the next screen. You will be taken a new spreadsheet with  blank Pivot Table canvas.   Here, using the Pivot...
Post a Comment
Read more

How to create a Waterfall Chart

Download Example Waterfall chart file from below link https://drive.google.com/file/d/17OKYxHKzT8NxWM0FuPqEb26ntzQqa29_/view?usp=sharing How to Create a Waterfall Chart in Excel If you want to build a waterfall chart of your own, we’ve got the step-by-step instructions for you. Although Excel 2016 includes a waterfall chart type within the chart options, if you’re working with any version older than that, you will need to construct the waterfall chart from scratch.  Step 1: Create a data table Let’s start with a simple table like annual sales numbers for the current year. You will see in the table below that the sales amounts vary for each month. Some months will have positive sales growth, while others will be negative.     Insert three additional columns to your Excel table to represent the movement of the columns on the waterfall chart. The base column will represent the starting point for the fall and rise of the chart. You will input all the negative numbers fro...
Post a Comment
Read more

Send Bulk Email from Excel for Outlook

  Download the File from below Link https://drive.google.com/file/d/1tcb4lzNFgEfDKsvQqCW05sgoiGFEhqcK/view?usp=sharing Instructions are given in the image below. Save the File as Excel Macro - Enabled workbook (.xlsm) Use this file to send bulk emails at a time i personally have sent more than 2000 bulk emails at a time. Error may occur if email id typed contains space etc. Only one email id one cell.
Post a Comment
Read more