time intelligence Archives - Dutch Data Dude

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.

Download the full Power BI file here or get just the DAX formulas. Enjoy!

Basic Measures

Total Amount:

C#
TotalAmount = SUM(Sales[Amount])
1
TotalAmount = SUM(Sales[Amount])
Total Quantity:

C#
TotalQuantity = SUM(Sales[Quantity])
1
TotalQuantity = SUM(Sales[Quantity])

Day Measures

Total Amount for Last Day (Yesterday)

C#
Amount_LastDay = CALCULATE([TotalAmount],PREVIOUSDAY('Date'[Date]))
1
Amount_LastDay = CALCULATE([TotalAmount],PREVIOUSDAY('Date'[Date]))
Total Amount for same Day last Year

C#
Amount_SameDayLastYear = CALCULATE([TotalAmount],SAMEPERIODLASTYEAR('Date'[Date]))
1
Amount_SameDayLastYear = CALCULATE([TotalAmount],SAMEPERIODLASTYEAR('Date'[Date]))
Variance of total Amount compared to Total Amount for Last Day

C#
Amount_DOD_Variance = [TotalAmount]-[Amount_LastDay]
1
Amount_DOD_Variance = [TotalAmount]-[Amount_LastDay]
Variance % of total Amount compared to Total Amount for Last Day

C#
Amount_DOD_Variance% = DIVIDE([Amount_DOD_Variance],[Amount_LastDay])
1
Amount_DOD_Variance% = DIVIDE([Amount_DOD_Variance],[Amount_LastDay])
Variance of total Amount compared to Total Amount for same Day last Year

C#
Amount_YOY_Variance = [TotalAmount]-[Amount_SameDayLastYear]
1
Amount_YOY_Variance = [TotalAmount]-[Amount_SameDayLastYear]
Variance % of total Amount compared to Total Amount same Day last Year

C#
Amount_YOY_Variance% = DIVIDE([Amount_YOY_Variance],[Amount_LastDay])
1
Amount_YOY_Variance% = DIVIDE([Amount_YOY_Variance],[Amount_LastDay])
Total Quantity for Last Day (Yesterday)

C#
Quantity_LastDay = CALCULATE([TotalQuantity],PREVIOUSDAY('Date'[Date]))
1
Quantity_LastDay = CALCULATE([TotalQuantity],PREVIOUSDAY('Date'[Date]))
Total Quantity for same Day last Year

C#
Quantity_SameDayLastYear = CALCULATE([TotalQuantity],SAMEPERIODLASTYEAR('Date'[Date]))
1
Quantity_SameDayLastYear = CALCULATE([TotalQuantity],SAMEPERIODLASTYEAR('Date'[Date]))
Variance of total Quantity compared to Total Quantity for Last Day

C#
Quantity_DOD_Variance = [TotalQuantity]-[Quantity_LastDay]
1
Quantity_DOD_Variance = [TotalQuantity]-[Quantity_LastDay]
Variance % of total Quantity compared to Total Quantity for Last Day

C#
Quantity_DOD_Variance% = DIVIDE([Quantity_DOD_Variance],[Quantity_LastDay])
1
Quantity_DOD_Variance% = DIVIDE([Quantity_DOD_Variance],[Quantity_LastDay])
Variance of total Quantity compared to total Quantity for same Day last Year

C#
Quantity_YOY_Variance = [TotalQuantity]-[Quantity_SameDayLastYear]
1
Quantity_YOY_Variance = [TotalQuantity]-[Quantity_SameDayLastYear]
Variance % of total Quantity compared to total Quantity for same Day last Year

C#
Quantity_YOY_Variance% = DIVIDE([Quantity_YOY_Variance],[Quantity_LastDay])
1
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] )
        )
    ))

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])

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] )
        )
    ))

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

C#
Amount_WTD_WOW_Variance = [Amount_WTD]-[Amount_LastWeek]
1
Amount_WTD_WOW_Variance = [Amount_WTD]-[Amount_LastWeek]
Variance % of total Amount Week To Date compared to Total Amount for Last Week

C#
Amount_WTD_WOW_Variance% = DIVIDE([Amount_WTD_WOW_Variance],[Amount_LastWeek])
1
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

C#
Amount_WTD_YOY_Variance = [Amount_WTD]-[Amount_SameWeekLastYear]
1
Amount_WTD_YOY_Variance = [Amount_WTD]-[Amount_SameWeekLastYear]
Variance % of total Amount Week to Date compared to Total Amount for same Week last Year

C#
Amount_WTD_YOY_Variance% = DIVIDE([Amount_WTD_YOY_Variance],[Amount_SameWeekLastYear])
1
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 ()
)

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] )
        )
    ))

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])

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

C#
Quantity_WTD_WOW_Variance = [Quantity_WTD]-[Quantity_LastWeek]
1
Quantity_WTD_WOW_Variance = [Quantity_WTD]-[Quantity_LastWeek]
Variance % of total Quantity Week To Date compared to Total Quantity for Last Week

C#
Quantity_WTD_WOW_Variance% = DIVIDE([Quantity_WTD_WOW_Variance],[Quantity_LastWeek])
1
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

C#
Quantity_WTD_YOY_Variance = [Quantity_WTD]-[Quantity_SameWeekLastYear]
1
Quantity_WTD_YOY_Variance = [Quantity_WTD]-[Quantity_SameWeekLastYear]
Variance % of total Quantity Week to Date compared to Total Quantity for same Week last Year

C#
Quantity_WTD_YOY_Variance% = DIVIDE([Quantity_WTD_YOY_Variance],[Quantity_SameWeekLastYear])
1
Quantity_WTD_YOY_Variance% = DIVIDE([Quantity_WTD_YOY_Variance],[Quantity_SameWeekLastYear])

Month Measures

Total Amount Month To Date

C#
Amount_MTD = TOTALMTD([TotalAmount],'Date'[Date])
1
Amount_MTD = TOTALMTD([TotalAmount],'Date'[Date])
Total Amount for same Month Last Year

C#
Amount_SameMonthLastYear = CALCULATE([Amount_MTD],SAMEPERIODLASTYEAR('Date'[Date]))
1
Amount_SameMonthLastYear = CALCULATE([Amount_MTD],SAMEPERIODLASTYEAR('Date'[Date]))
Total Amount for Last Month

C#
Amount_LastMonth = CALCULATE([TotalAmount],PREVIOUSMONTH('Date'[Date]))
1
Amount_LastMonth = CALCULATE([TotalAmount],PREVIOUSMONTH('Date'[Date]))
Variance of total Amount Month To Date compared to Total Amount for Last Month

C#
Amount_MTD_MOM_Variance = [Amount_MTD]-[Amount_LastMonth]
1
Amount_MTD_MOM_Variance = [Amount_MTD]-[Amount_LastMonth]
Variance % of total Amount Month To Date compared to Total Amount for Last Month

C#
Amount_MTD_MOM_Variance% = DIVIDE([Amount_MTD_MOM_Variance],[Amount_LastMonth])
1
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

C#
Amount_MTD_YOY_Variance = [Amount_MTD]-[Amount_SameMonthLastYear]
1
Amount_MTD_YOY_Variance = [Amount_MTD]-[Amount_SameMonthLastYear]
Variance % of total Amount Month to Date compared to Total Amount for same Month last Year

C#
Amount_MTD_YOY_Variance% = DIVIDE([Amount_MTD_YOY_Variance],[Amount_SameMonthLastYear])
1
Amount_MTD_YOY_Variance% = DIVIDE([Amount_MTD_YOY_Variance],[Amount_SameMonthLastYear])
Total Quantity Month To Date

C#
Quantity_MTD = TOTALMTD([TotalQuantity],'Date'[Date])
1
Quantity_MTD = TOTALMTD([TotalQuantity],'Date'[Date])
Total Quantity for same Month Last Year

C#
Quantity_SameMonthLastYear = CALCULATE([Quantity_MTD],SAMEPERIODLASTYEAR('Date'[Date]))
1
Quantity_SameMonthLastYear = CALCULATE([Quantity_MTD],SAMEPERIODLASTYEAR('Date'[Date]))
Total Quantity for Last Month

C#
Quantity_LastMonth = CALCULATE([TotalQuantity],PREVIOUSMONTH('Date'[Date]))
1
Quantity_LastMonth = CALCULATE([TotalQuantity],PREVIOUSMONTH('Date'[Date]))
Variance of total Quantity Month To Date compared to Total Quantity for Last Month

C#
Quantity_MTD_MOM_Variance = [Quantity_MTD]-[Quantity_LastMonth]
1
Quantity_MTD_MOM_Variance = [Quantity_MTD]-[Quantity_LastMonth]
Variance % of total Quantity Month To Date compared to Total Quantity for Last Month

C#
Quantity_MTD_MOM_Variance% = DIVIDE([Quantity_MTD_MOM_Variance],[Quantity_LastMonth])
1
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

C#
Quantity_MTD_YOY_Variance = [Quantity_MTD]-[Quantity_SameMonthLastYear]
1
Quantity_MTD_YOY_Variance = [Quantity_MTD]-[Quantity_SameMonthLastYear]
Variance % of total Quantity Month to Date compared to Total Quantity for same Month last Year

C#
Quantity_MTD_YOY_Variance% = DIVIDE([Quantity_MTD_YOY_Variance],[Quantity_SameMonthLastYear])
1
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 ()
)

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

C#
Amount_SamePeriodLastYear = CALCULATE([Amount_PTD],SAMEPERIODLASTYEAR('Date'[Date]))
1
Amount_SamePeriodLastYear = CALCULATE([Amount_PTD],SAMEPERIODLASTYEAR('Date'[Date]))
Total Amount for Last Period

C#
Amount_LastPeriod = CALCULATE([TotalAmount],DATEADD('Date'[Date],-2,MONTH))
1
Amount_LastPeriod = CALCULATE([TotalAmount],DATEADD('Date'[Date],-2,MONTH))
Variance of total Amount Period To Date compared to Total Amount for Last Period

C#
Amount_PTD_POP_Variance = [Amount_PTD]-[Amount_LastPeriod]
1
Amount_PTD_POP_Variance = [Amount_PTD]-[Amount_LastPeriod]
Variance % of total Amount Period To Date compared to Total Amount for Last Period

C#
Amount_PTD_POP_Variance% = DIVIDE([Amount_PTD_POP_Variance],[Amount_LastPeriod])
1
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

C#
Amount_PTD_YOY_Variance = [Amount_PTD]-[Amount_SamePeriodLastYear]
1
Amount_PTD_YOY_Variance = [Amount_PTD]-[Amount_SamePeriodLastYear]
Variance % of total Amount Period to Date compared to Total Amount for same Period last Year

C#
Amount_PTD_YOY_Variance% = DIVIDE([Amount_PTD_YOY_Variance],[Amount_SamePeriodLastYear])
1
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 ()
)

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

C#
Quantity_SamePeriodLastYear = CALCULATE([Quantity_PTD],SAMEPERIODLASTYEAR('Date'[Date]))
1
Quantity_SamePeriodLastYear = CALCULATE([Quantity_PTD],SAMEPERIODLASTYEAR('Date'[Date]))
Total Quantity for Last Period

C#
Quantity_LastPeriod = CALCULATE([TotalQuantity],DATEADD('Date'[Date],-2,MONTH))
1
Quantity_LastPeriod = CALCULATE([TotalQuantity],DATEADD('Date'[Date],-2,MONTH))
Variance of total Quantity Period To Date compared to Total Quantity for Last Period

C#
Quantity_PTD_POP_Variance = [Quantity_PTD]-[Quantity_LastPeriod]
1
Quantity_PTD_POP_Variance = [Quantity_PTD]-[Quantity_LastPeriod]
Variance % of total Quantity Period To Date compared to Total Quantity for Last Period

C#
Quantity_PTD_POP_Variance% = DIVIDE([Quantity_PTD_POP_Variance],[Quantity_LastPeriod])
1
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

C#
Quantity_PTD_YOY_Variance = [Quantity_PTD]-[Quantity_SamePeriodLastYear]
1
Quantity_PTD_YOY_Variance = [Quantity_PTD]-[Quantity_SamePeriodLastYear]
Variance of total Quantity Period to Date compared to Total Quantity for same Period last Year

C#
Quantity_PTD_YOY_Variance% = DIVIDE([Quantity_PTD_YOY_Variance],[Quantity_SamePeriodLastYear])
1
Quantity_PTD_YOY_Variance% = DIVIDE([Quantity_PTD_YOY_Variance],[Quantity_SamePeriodLastYear])

Quarter Measures

Total Amount Quarter To Date

C#
Amount_QTD = TOTALQTD([TotalAmount],'Date'[Date])
1
Amount_QTD = TOTALQTD([TotalAmount],'Date'[Date])
Total Amount for same Quarter Last Year

C#
Amount_SameQuarterLastYear = CALCULATE([Amount_QTD],SAMEPERIODLASTYEAR('Date'[Date]))
1
Amount_SameQuarterLastYear = CALCULATE([Amount_QTD],SAMEPERIODLASTYEAR('Date'[Date]))
Total Amount for Last Quarter

C#
Amount_LastQuarter = CALCULATE([TotalAmount],PREVIOUSQUARTER('Date'[Date]))
1
Amount_LastQuarter = CALCULATE([TotalAmount],PREVIOUSQUARTER('Date'[Date]))
Variance of total Amount Quarter To Date compared to Total Amount for Last Quarter

C#
Amount_QTD_QOQ_Variance = [Amount_QTD]-[Amount_LastQuarter]
1
Amount_QTD_QOQ_Variance = [Amount_QTD]-[Amount_LastQuarter]
Variance % of total Amount Quarter To Date compared to Total Amount for Last Quarter

C#
Amount_QTD_QOQ_Variance% = DIVIDE([Amount_QTD_QOQ_Variance],[Amount_LastQuarter])
1
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

C#
Amount_QTD_YOY_Variance = [Amount_QTD]-[Amount_SameQuarterLastYear]
1
Amount_QTD_YOY_Variance = [Amount_QTD]-[Amount_SameQuarterLastYear]
Variance % of total Amount Quarter to Date compared to Total Amount for same Quarter last Year

C#
Amount_QTD_YOY_Variance% = DIVIDE([Amount_QTD_YOY_Variance],[Amount_SameQuarterLastYear])
1
Amount_QTD_YOY_Variance% = DIVIDE([Amount_QTD_YOY_Variance],[Amount_SameQuarterLastYear])
Total Quantity Quarter To Date

C#
Quantity_QTD = TOTALQTD([TotalQuantity],'Date'[Date])
1
Quantity_QTD = TOTALQTD([TotalQuantity],'Date'[Date])
Total Quantity for same Quarter Last Year

C#
Quantity_SameQuarterLastYear = CALCULATE([Quantity_QTD],SAMEPERIODLASTYEAR('Date'[Date]))
1
Quantity_SameQuarterLastYear = CALCULATE([Quantity_QTD],SAMEPERIODLASTYEAR('Date'[Date]))
Total Quantity for Last Quarter

C#
Quantity_LastQuarter = CALCULATE([TotalQuantity],PREVIOUSQUARTER('Date'[Date]))
1
Quantity_LastQuarter = CALCULATE([TotalQuantity],PREVIOUSQUARTER('Date'[Date]))
Variance of total Quantity Quarter To Date compared to Total Quantity for Last Quarter

C#
Quantity_QTD_QOQ_Variance = [Quantity_QTD]-[Quantity_LastQuarter]
1
Quantity_QTD_QOQ_Variance = [Quantity_QTD]-[Quantity_LastQuarter]
Variance % of total Quantity Quarter To Date compared to Total Quantity for Last Quarter

C#
Quantity_QTD_QOQ_Variance% = DIVIDE([Quantity_QTD_QOQ_Variance],[Quantity_LastQuarter])
1
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

C#
Quantity_QTD_YOY_Variance = [Quantity_QTD]-[Quantity_SameQuarterLastYear]
1
Quantity_QTD_YOY_Variance = [Quantity_QTD]-[Quantity_SameQuarterLastYear]
Variance % of total Quantity Quarter To Date compared to Total Quantity for same Quarter last Year

C#
Quantity_QTD_YOY_Variance% = DIVIDE([Quantity_QTD_YOY_Variance],[Quantity_SameQuarterLastYear])
1
Quantity_QTD_YOY_Variance% = DIVIDE([Quantity_QTD_YOY_Variance],[Quantity_SameQuarterLastYear])

Year Measures

Total Amount Year To Date

C#
Amount_YTD = TOTALYTD([TotalAmount],'Date'[Date])
1
Amount_YTD = TOTALYTD([TotalAmount],'Date'[Date])
Total Amount for Last Year

C#
Amount_LastYear = CALCULATE([Amount_YTD],PREVIOUSYEAR('Date'[Date]))
1
Amount_LastYear = CALCULATE([Amount_YTD],PREVIOUSYEAR('Date'[Date]))
Variance of total Amount Year To Date compared to Total Amount for Last Year

C#
Amount_YTD_YOY_Variance = [Amount_YTD]-[Amount_LastYear]
1
Amount_YTD_YOY_Variance = [Amount_YTD]-[Amount_LastYear]
Variance% of total Amount Year To Date compared to Total Amount for Last Year

C#
Amount_YTD_YOY_Variance% = DIVIDE([Amount_YTD_YOY_Variance],[Amount_LastYear])
1
Amount_YTD_YOY_Variance% = DIVIDE([Amount_YTD_YOY_Variance],[Amount_LastYear])
Total Quantity Year To Date

C#
Quantity_YTD = TOTALYTD([TotalQuantity],'Date'[Date])
1
Quantity_YTD = TOTALYTD([TotalQuantity],'Date'[Date])
Total Quantity for Last Year

C#
Quantity_LastYear = CALCULATE([TotalQuantity],PREVIOUSYEAR('Date'[Date]))
1
Quantity_LastYear = CALCULATE([TotalQuantity],PREVIOUSYEAR('Date'[Date]))
Variance of total Quantity Year To Date compared to Total Quantity for Last Year

C#
Quantity_YTD_YOY_Variance = -[Quantity_YTD]-[Quantity_LastYear]
1
Quantity_YTD_YOY_Variance = -[Quantity_YTD]-[Quantity_LastYear]
Variance% of total Quantity Year To Date compared to Total Quantity for Last Year

C#
Quantity_YTD_YOY_Variance% = DIVIDE([Quantity_YTD_YOY_Variance],[Quantity_LastYear])
1
Quantity_YTD_YOY_Variance% = DIVIDE([Quantity_YTD_YOY_Variance],[Quantity_LastYear])