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

Recover Unsaved Excel File

We normally avoid these settings but in the hour of need they save us rework time. So if your working on a excel file and you forget to save it or click on don't save by mistake, follow below steps  If the workbook was open for at least 10 minutes and created an AutoRecover version, Excel kept a copy for you. Follow these steps to get it back: Open Excel. In the left panel, choose Open Other Workbooks. In the center panel, scroll all the way to the bottom of the recent files. At the very end, click Recover Unsaved Workbooks. Excel shows you all the unsaved workbooks that it has saved for you recently. Click a workbook and choose Open. If it is the wrong one, go back to File, Open and scroll to the bottom of the list. When you find the right file, click the Save As button to save the workbook. Unsaved workbooks are saved for four days before they are automatically deleted. Use AutoRecover Versions to Recover Files Previously Saved Recover Unsaved Workbooks applies only to files that...
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