ULTIMATE TIME BASED CALCULATIONS CHEAT SHEET FOR DAX / POWER BI (INCLUDING WEEK BASED CALCULATIONS)

 


ULTIMATE TIME BASED CALCULATIONS CHEAT SHEET FOR DAX / POWER BI (INCLUDING WEEK BASED CALCULATIONS)

 

Power BI provides great time intelligence features to calculate Year-to-date (YTD), Month-to-date (MTD) and Quarter-to-date (QTD) totals. There is no such thing as Week-to-date (WTD) or Period-to-date (PTD) where period could be any arbitrary period definition (I used two-month periods in my example below). If you want those, you will have to create the calculations yourself. I was inspired by this excellent blog post and created an ultimate time-intelligence calculations Power BI file. I used Matt Massons excellent date dimension generation script to generate the date table for my example.

Basic Measures

  • Total Amount:

TotalAmount = SUM(Sales[Amount])

  • Total Quantity:

TotalQuantity = SUM(Sales[Quantity])

Day Measures

  • Total Amount for Last Day (Yesterday)

Amount_LastDay = CALCULATE([TotalAmount],PREVIOUSDAY('Date'[Date]))

  • Total Amount for same Day last Year

Amount_SameDayLastYear = CALCULATE([TotalAmount],SAMEPERIODLASTYEAR('Date'[Date]))

  • Variance of total Amount compared to Total Amount for Last Day

Amount_DOD_Variance = [TotalAmount]-[Amount_LastDay]

  • Variance % of total Amount compared to Total Amount for Last Day

Amount_DOD_Variance% = DIVIDE([Amount_DOD_Variance],[Amount_LastDay])

  • Variance of total Amount compared to Total Amount for same Day last Year

Amount_YOY_Variance = [TotalAmount]-[Amount_SameDayLastYear]

  • Variance % of total Amount compared to Total Amount same Day last Year

Amount_YOY_Variance% = DIVIDE([Amount_YOY_Variance],[Amount_LastDay])

  • Total Quantity for Last Day (Yesterday)

Quantity_LastDay = CALCULATE([TotalQuantity],PREVIOUSDAY('Date'[Date]))

  • Total Quantity for same Day last Year

Quantity_SameDayLastYear = CALCULATE([TotalQuantity],SAMEPERIODLASTYEAR('Date'[Date]))

  • Variance of total Quantity compared to Total Quantity for Last Day

Quantity_DOD_Variance = [TotalQuantity]-[Quantity_LastDay]

  • Variance % of total Quantity compared to Total Quantity for Last Day

Quantity_DOD_Variance% = DIVIDE([Quantity_DOD_Variance],[Quantity_LastDay])

  • Variance of total Quantity compared to total Quantity for same Day last Year

Quantity_YOY_Variance = [TotalQuantity]-[Quantity_SameDayLastYear]

  • Variance % of total Quantity compared to total Quantity for same Day last Year

Quantity_YOY_Variance% = DIVIDE([Quantity_YOY_Variance],[Quantity_LastDay])

Week Measures

  • Total Amount Week To Date

·         Amount_WTD = IF (

·             HASONEVALUE ( 'Date'[Year] )

·                 && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE(

·                 [TotalAmount],

·                 FILTER (

·                     ALL ( 'Date' ),

·                     'Date'[Year] = VALUES ( 'Date'[Year] )

·                         && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] )

·                         && 'Date'[Date] <= MAX ( 'Date'[Date] )

·                 )

    ))

  • Total Amount for Last Week

·         Amount_LastWeek = SUMX(

·             FILTER(ALL('Date'),

·                 IF(SELECTEDVALUE('Date'[WeekNumber])=1,

·                     'Date'[WeekNumber]=CALCULATE(MAX('Date'[WeekNumber]), ALL('Date')) && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year]))-1,""),

·                     'Date'[WeekNumber]=SELECTEDVALUE('Date'[WeekNumber])-1 && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year])),""))

·             ),

    [TotalAmount])

  • Total Amount for same Week Last Year

·         Amount_SameWeekLastYear = IF (

·             HASONEVALUE ( 'Date'[Year] )

·                 && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE(

·                 SUM ( Sales[Amount] ),

·                 FILTER (

·                     ALL ( 'Date' ),

·                     'Date'[Year] = FORMAT(VALUES ( 'Date'[Year] )-1,"")

·                         && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] )

·                         && 'Date'[Date] <= MAX ( 'Date'[Date] )

·                 )

    ))

  • Variance of total Amount Week To Date compared to Total Amount for Last Week

Amount_WTD_WOW_Variance = [Amount_WTD]-[Amount_LastWeek]

  • Variance % of total Amount Week To Date compared to Total Amount for Last Week

Amount_WTD_WOW_Variance% = DIVIDE([Amount_WTD_WOW_Variance],[Amount_LastWeek])

  • Variance of total Amount Week to Date compared to Total Amount for same Week last Year

Amount_WTD_YOY_Variance = [Amount_WTD]-[Amount_SameWeekLastYear]

  • Variance % of total Amount Week to Date compared to Total Amount for same Week last Year

Amount_WTD_YOY_Variance% = DIVIDE([Amount_WTD_YOY_Variance],[Amount_SameWeekLastYear])

  • Total Quantity Week To Date

·         Quantity_WTD = IF (

·             HASONEVALUE ( 'Date'[Year] )

·                 && HASONEVALUE ('Date'[WeekNumber] ),

·             CALCULATE (

·                 [TotalQuantity],

·                 FILTER (

·                     ALL ( 'Date' ),

·                     'Date'[Year] = VALUES ( 'Date'[Year] )

·                         && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] )

·                         && 'Date'[Date] <= MAX ( 'Date'[Date] )

·                 )

·             ),

·             BLANK ()

)

  • Total Quantity for same Week Last Year

·         Quantity_SameWeekLastYear = IF (

·             HASONEVALUE ( 'Date'[Year] )

·                 && HASONEVALUE ('Date'[WeekNumber] ), CALCULATE(

·                 SUM ( Sales[Quantity] ),

·                 FILTER (

·                     ALL ( 'Date' ),

·                     'Date'[Year] = FORMAT(VALUES ( 'Date'[Year] )-1,"")

·                         && 'Date'[WeekNumber] = VALUES ( 'Date'[WeekNumber] )

·                         && 'Date'[Date] <= MAX ( 'Date'[Date] )

·                 )

    ))

  • Total Quantity for Last Week

·         Quantity_LastWeek = SUMX(

·             FILTER(ALL('Date'),

·                 IF(SELECTEDVALUE('Date'[WeekNumber])=1,

·                     'Date'[WeekNumber]=CALCULATE(MAX('Date'[WeekNumber]), ALL('Date')) && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year]))-1,""),

·                     'Date'[WeekNumber]=SELECTEDVALUE('Date'[WeekNumber])-1 && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year])),""))

·             ),

    [TotalQuantity])

  • Variance of total Quantity Week To Date compared to Total Quantity for Last Week

Quantity_WTD_WOW_Variance = [Quantity_WTD]-[Quantity_LastWeek]

  • Variance % of total Quantity Week To Date compared to Total Quantity for Last Week

Quantity_WTD_WOW_Variance% = DIVIDE([Quantity_WTD_WOW_Variance],[Quantity_LastWeek])

  • Variance of total Quantity Week to Date compared to Total Quantity for same Week last Year

Quantity_WTD_YOY_Variance = [Quantity_WTD]-[Quantity_SameWeekLastYear]

  • Variance % of total Quantity Week to Date compared to Total Quantity for same Week last Year

Quantity_WTD_YOY_Variance% = DIVIDE([Quantity_WTD_YOY_Variance],[Quantity_SameWeekLastYear])

Month Measures

  • Total Amount Month To Date

Amount_MTD = TOTALMTD([TotalAmount],'Date'[Date])

  • Total Amount for same Month Last Year

Amount_SameMonthLastYear = CALCULATE([Amount_MTD],SAMEPERIODLASTYEAR('Date'[Date]))

  • Total Amount for Last Month

Amount_LastMonth = CALCULATE([TotalAmount],PREVIOUSMONTH('Date'[Date]))

  • Variance of total Amount Month To Date compared to Total Amount for Last Month

Amount_MTD_MOM_Variance = [Amount_MTD]-[Amount_LastMonth]

  • Variance % of total Amount Month To Date compared to Total Amount for Last Month

Amount_MTD_MOM_Variance% = DIVIDE([Amount_MTD_MOM_Variance],[Amount_LastMonth])

  • Variance of total Amount Month to Date compared to Total Amount for same Month last Year

Amount_MTD_YOY_Variance = [Amount_MTD]-[Amount_SameMonthLastYear]

  • Variance % of total Amount Month to Date compared to Total Amount for same Month last Year

Amount_MTD_YOY_Variance% = DIVIDE([Amount_MTD_YOY_Variance],[Amount_SameMonthLastYear])

  • Total Quantity Month To Date

Quantity_MTD = TOTALMTD([TotalQuantity],'Date'[Date])

  • Total Quantity for same Month Last Year

Quantity_SameMonthLastYear = CALCULATE([Quantity_MTD],SAMEPERIODLASTYEAR('Date'[Date]))

  • Total Quantity for Last Month

Quantity_LastMonth = CALCULATE([TotalQuantity],PREVIOUSMONTH('Date'[Date]))

  • Variance of total Quantity Month To Date compared to Total Quantity for Last Month

Quantity_MTD_MOM_Variance = [Quantity_MTD]-[Quantity_LastMonth]

  • Variance % of total Quantity Month To Date compared to Total Quantity for Last Month

Quantity_MTD_MOM_Variance% = DIVIDE([Quantity_MTD_MOM_Variance],[Quantity_LastMonth])

  • Variance of total Quantity Month to Date compared to Total Quantity for same Month last Year

Quantity_MTD_YOY_Variance = [Quantity_MTD]-[Quantity_SameMonthLastYear]

  • Variance % of total Quantity Month to Date compared to Total Quantity for same Month last Year

Quantity_MTD_YOY_Variance% = DIVIDE([Quantity_MTD_YOY_Variance],[Quantity_SameMonthLastYear])

Period Measures

Total Amount Period To Date
·       Amount_PTD = IF (
·             HASONEVALUE ( 'Date'[Year] )
·                 && HASONEVALUE ('Date'[TwoMonthPeriod] ),
·             CALCULATE (
·                 [TotalAmount],
·                 FILTER (
·                     ALL ( 'Date' ),
·                     'Date'[Year] = VALUES ( 'Date'[Year] )
·                         && 'Date'[TwoMonthPeriod] = VALUES ( 'Date'[TwoMonthPeriod] )
·                         && 'Date'[Date] <= MAX ( 'Date'[Date] )
·                 )
·             ),
·             BLANK ()
)
  • Total Amount for same Period Last Year

Amount_SamePeriodLastYear = CALCULATE([Amount_PTD],SAMEPERIODLASTYEAR('Date'[Date]))

  • Total Amount for Last Period

Amount_LastPeriod = CALCULATE([TotalAmount],DATEADD('Date'[Date],-2,MONTH))

  • Variance of total Amount Period To Date compared to Total Amount for Last Period

Amount_PTD_POP_Variance = [Amount_PTD]-[Amount_LastPeriod]

  • Variance % of total Amount Period To Date compared to Total Amount for Last Period

Amount_PTD_POP_Variance% = DIVIDE([Amount_PTD_POP_Variance],[Amount_LastPeriod])

  • Variance of total Amount Period to Date compared to Total Amount for same Period last Year

Amount_PTD_YOY_Variance = [Amount_PTD]-[Amount_SamePeriodLastYear]

  • Variance % of total Amount Period to Date compared to Total Amount for same Period last Year

Amount_PTD_YOY_Variance% = DIVIDE([Amount_PTD_YOY_Variance],[Amount_SamePeriodLastYear])

  • Total Quantity Period To Date

·         Quantity_PTD = IF (

·             HASONEVALUE ( 'Date'[Year] )

·                 && HASONEVALUE ('Date'[TwoMonthPeriod] ),

·             CALCULATE (

·                 [TotalQuantity],

·                 FILTER (

·                     ALL ( 'Date' ),

·                     'Date'[Year] = VALUES ( 'Date'[Year] )

·                         && 'Date'[TwoMonthPeriod] = VALUES ( 'Date'[TwoMonthPeriod] )

·                         && 'Date'[Date] <= MAX ( 'Date'[Date] )

·                 )

·             ),

·             BLANK ()

)

  • Total Quantity for same Period Last Year

Quantity_SamePeriodLastYear = CALCULATE([Quantity_PTD],SAMEPERIODLASTYEAR('Date'[Date]))

  • Total Quantity for Last Period

Quantity_LastPeriod = CALCULATE([TotalQuantity],DATEADD('Date'[Date],-2,MONTH))

  • Variance of total Quantity Period To Date compared to Total Quantity for Last Period

Quantity_PTD_POP_Variance = [Quantity_PTD]-[Quantity_LastPeriod]

  • Variance % of total Quantity Period To Date compared to Total Quantity for Last Period

Quantity_PTD_POP_Variance% = DIVIDE([Quantity_PTD_POP_Variance],[Quantity_LastPeriod])

  • Variance % of total Quantity Period to Date compared to Total Quantity for same Period last Year

Quantity_PTD_YOY_Variance = [Quantity_PTD]-[Quantity_SamePeriodLastYear]

  • Variance of total Quantity Period to Date compared to Total Quantity for same Period last Year

Quantity_PTD_YOY_Variance% = DIVIDE([Quantity_PTD_YOY_Variance],[Quantity_SamePeriodLastYear])

Quarter Measures

  • Total Amount Quarter To Date

Amount_QTD = TOTALQTD([TotalAmount],'Date'[Date])

  • Total Amount for same Quarter Last Year

Amount_SameQuarterLastYear = CALCULATE([Amount_QTD],SAMEPERIODLASTYEAR('Date'[Date]))

  • Total Amount for Last Quarter

Amount_LastQuarter = CALCULATE([TotalAmount],PREVIOUSQUARTER('Date'[Date]))

  • Variance of total Amount Quarter To Date compared to Total Amount for Last Quarter

Amount_QTD_QOQ_Variance = [Amount_QTD]-[Amount_LastQuarter]

  • Variance % of total Amount Quarter To Date compared to Total Amount for Last Quarter

Amount_QTD_QOQ_Variance% = DIVIDE([Amount_QTD_QOQ_Variance],[Amount_LastQuarter])

  • Variance of total Amount Quarter to Date compared to Total Amount for same Quarter last Year

Amount_QTD_YOY_Variance = [Amount_QTD]-[Amount_SameQuarterLastYear]

  • Variance % of total Amount Quarter to Date compared to Total Amount for same Quarter last Year

Amount_QTD_YOY_Variance% = DIVIDE([Amount_QTD_YOY_Variance],[Amount_SameQuarterLastYear])

  • Total Quantity Quarter To Date

Quantity_QTD = TOTALQTD([TotalQuantity],'Date'[Date])

  • Total Quantity for same Quarter Last Year

Quantity_SameQuarterLastYear = CALCULATE([Quantity_QTD],SAMEPERIODLASTYEAR('Date'[Date]))

  • Total Quantity for Last Quarter

Quantity_LastQuarter = CALCULATE([TotalQuantity],PREVIOUSQUARTER('Date'[Date]))

  • Variance of total Quantity Quarter To Date compared to Total Quantity for Last Quarter

Quantity_QTD_QOQ_Variance = [Quantity_QTD]-[Quantity_LastQuarter]

  • Variance % of total Quantity Quarter To Date compared to Total Quantity for Last Quarter

Quantity_QTD_QOQ_Variance% = DIVIDE([Quantity_QTD_QOQ_Variance],[Quantity_LastQuarter])

  • Variance of total Quantity Quarter To Date compared to Total Quantity or same Quarter last Year

Quantity_QTD_YOY_Variance = [Quantity_QTD]-[Quantity_SameQuarterLastYear]

  • Variance % of total Quantity Quarter To Date compared to Total Quantity for same Quarter last Year

Quantity_QTD_YOY_Variance% = DIVIDE([Quantity_QTD_YOY_Variance],[Quantity_SameQuarterLastYear])

Year Measures

  • Total Amount Year To Date

Amount_YTD = TOTALYTD([TotalAmount],'Date'[Date])

  • Total Amount for Last Year

Amount_LastYear = CALCULATE([Amount_YTD],PREVIOUSYEAR('Date'[Date]))

  • Variance of total Amount Year To Date compared to Total Amount for Last Year

Amount_YTD_YOY_Variance = [Amount_YTD]-[Amount_LastYear]

  • Variance% of total Amount Year To Date compared to Total Amount for Last Year

Amount_YTD_YOY_Variance% = DIVIDE([Amount_YTD_YOY_Variance],[Amount_LastYear])

  • Total Quantity Year To Date

Quantity_YTD = TOTALYTD([TotalQuantity],'Date'[Date])

  • Total Quantity for Last Year

Quantity_LastYear = CALCULATE([TotalQuantity],PREVIOUSYEAR('Date'[Date]))

  • Variance of total Quantity Year To Date compared to Total Quantity for Last Year

Quantity_YTD_YOY_Variance = -[Quantity_YTD]-[Quantity_LastYear]

  • Variance% of total Quantity Year To Date compared to Total Quantity for Last Year

Quantity_YTD_YOY_Variance% = DIVIDE([Quantity_YTD_YOY_Variance],[Quantity_LastYear])

 

Comments

Post a Comment

Popular posts from this blog

What is Gini Impurity?

Image
  Gini Impurity A measurement used to build Decision Trees to determine how the features of a dataset should split nodes to form the tree. What is Gini Impurity? Gini Impurity is a measurement used to build Decision Trees to determine how the features of a dataset should split nodes to form the tree. More precisely, the Gini Impurity of a dataset is a number between 0-0.5, which indicates the likelihood of new, random data being misclassified if it were given a random class label according to the class distribution in the dataset. For example, say you want to build a classifier that determines if someone will default on their credit card. You have some labeled data with features, such as bins for age, income, credit rating, and whether or not each person is a student. To find the best feature for the first split of the tree – the root node – you could calculate how poorly each feature divided the data into the correct class, default ("yes") or didn't default ("no&quo
Read more

20+ Questions to Test your Skills on Logistic Regression

Image
1. What do you mean by the Logistic Regression? It’s a classification algorithm that is used where the target variable is of categorical nature. The main objective behind Logistic Regression is to determine the relationship between features and the probability of a particular outcome. For Example,  when we need to predict whether a student passes or fails in an exam given the number of hours spent studying as a feature, the target variable comprises two values i.e. pass and fail. Therefore, we can solve classification problem statements which is a supervised machine learning technique using Logistic Regression. 2. What are the different types of Logistic Regression? Three different types of Logistic Regression are as follows: 1. Binary Logistic Regression:  In this, the target variable has only two 2 possible outcomes. For Example,  0 and 1, or pass and fail or true and false. 2. Multinomial Logistic Regression:  In this, the target variable can have three or more possible values witho
Read more

Artificial Neural Network Interview Questions

Image
  Artificial Neural Network Interview Questions 1. Explain Neural Network?  Neural Network is a sequence of an algorithm that gives its best to recognize the underlying relationship in a data-set through a process. The artificial neural network has several differences from biological brains. These networks play a crucial role in deep learning.    2. Explain Biological Neural Network and Artificial Neural network? Biological Neural Network  is prepared with the help of real neurons. Our nervous system is made via neurons and brain. A biological neural network is our nervous system in which neurons are linked with each other. Human has a mind to understand and can operate any task in a particular situation, but how can a robot do that? For that purpose, an artificial brain is designed, which is known as  Neural Network . The human brain has neurons to transfer the information or data. Similarly, the neural network has nodes to perform the task. Nodes are the mathematical functions. 3. Wh
Read more