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

Improve your Excel Productivity with these Shortcuts and formulas

I have given below 45 tips and tricks to improve your productivity while working in Excel. Useful Keyboard Shortcuts 1.  To format any selected object , press ctrl+1 2.  To insert current date , press ctrl+; 3.  To insert current time , press ctrl+shift+; 4.  To repeat last action , press F4 5.  To edit a cell comment , press shift + F2 6.  To autosum selected cells , press alt + = 7.  To see the suggest drop-down in a cell , press alt + down arrow 8.  To enter multiple lines in a cell , press alt+enter 9.  To insert a new sheet , press shift + F11 10.  To edit active cell , press F2 (places cursor in the end) 11.  To hide current row , press ctrl+9 12.  To hide current column , press ctrl+0 13.  To unhide rows in selected range , press ctrl+shift+9 14.  To unhide columns in selected range , press ctrl+shift+0 15.  To recalculate formulas , press F9 16.  To select data in current region , press ctrl+shift+8 ...
Post a Comment
Read more

Data Analytics

Introduction to Data Analytics What is Data Analytics? Data Analytics is the process of exploring and analyzing large data sets to help data driven decision making.                         Analyze Data        Decision Making Definition Data when suitably filtered and analysed along with other related Data Sources and a suitable Analytics applied can provide valuable information to various organizations, industries, business, etc. in the form of prediction, recommendation, decision and the like. Applications of Data Analytics Finance & Accounting, Business analytics, Fraud , Healthcare, Information Technology, Insurance, Taxation , Internal Audit, Digital forensic, Transportation, Food, Delivery, FMCG, Planning of cities, Expenditure, Risk management, Risk detection, Security, Travelling, Managing Energy, Internet searching, Digital advertisement , etc. Real life examples of Data Analytics 1. ...
Post a Comment
Read more

Basics of Microsoft Excel

A. Microsoft Excel Basics of Excel   There are 5 important areas in the screen. 1. Quick Access Toolbar: This is a place where all the important tools can be placed. When you start Excel for the very first time, it has only 3 icons (Save, Undo, Redo). But you can add any feature of Excel to to Quick Access Toolbar so that you can easily access it from anywhere (hence the name). 2. Ribbon: Ribbon is like an expanded menu. It depicts all the features of Excel in easy to understand form. Since Excel has 1000s of features, they are grouped in to several ribbons. The most important ribbons are – Home, Insert, Formulas, Page Layout & Data. 3. Formula Bar: This is where any calculations or formulas you write will appear. You will understand the relevance of it once you start building formulas. 4. Spreadsheet Grid: This is where all your numbers, data, charts & drawings will go. Each Excel file can contain several sheets. But the spreadsheet grid shows few rows & column...
Post a Comment
Read more