This page is under construction. This tutorial provides a guideline to fundamental concepts in PowerBI.

Time Intelegence in DAX

let's add the group calculations below into tabular editor.

Current Year

CY = SELECTEDMEASURE()

Last Year

LY = 
var _currOffset = MAX( 'Calendar'[Fiscal Month Offset] )
var _offsetBegin = MIN( 'Calendar'[Fiscal Month Offset] ) - 12
var _offsetEnd = _currOffset - 12 
var _currYr = MAX( 'Calendar'[Fiscal Year Offset] )
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES( 'Calendar'[Date] )
    , ALL( 'Calendar' )
    , 'Calendar'[Fiscal Month Offset] >= _offsetBegin &&
        'Calendar'[Fiscal Month Offset] <= _offsetEnd
)
var _rslt = 
CALCULATE ( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    _rslt,
    _currOffset <= 0 || _currYr <= 0
)

Current vs Last Year

CY vs LY = 
var _currOffset = MAX( 'Calendar'[Sequential Fiscal Month] )
var _offsetBegin = MIN( 'Calendar'[Sequential Fiscal Month] ) - 12
var _offsetEnd = _currOffset - 12
var _cy = MAX( 'Calendar'[Fiscal Year Offset] )
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES( 'Calendar'[Date] )
    , ALL( 'Calendar' )
    , 'Calendar'[Sequential Fiscal Month] >= _offsetBegin &&
        'Calendar'[Sequential Fiscal Month] <= _offsetEnd
)
var _rslt = 
CALCULATE ( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    SELECTEDMEASURE() - _rslt,
    _cy <= 0 && _rslt
)

Current vs Last Year %

CY vs LY% = 
var _currOffset = MAX( 'Calendar'[Sequential Fiscal Month] )
var _offsetBegin = MIN( 'Calendar'[Sequential Fiscal Month] ) - 12
var _offsetEnd = _currOffset - 12
var _cy = MAX( 'Calendar'[Fiscal Year Offset] )
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES( 'Calendar'[Date] )
    , ALL( 'Calendar' )
    , 'Calendar'[Sequential Fiscal Month] >= _offsetBegin &&
        'Calendar'[Sequential Fiscal Month] <= _offsetEnd
)
var _rslt = 
CALCULATE ( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    DIVIDE( SELECTEDMEASURE() - _rslt, ABS(_rslt) ) ,
    _cy <= 0
)

Year to Day

YTD = 
var _currFiscYr = MAX( 'Calendar'[FY] )
var _currDay = MAX( 'Calendar'[Day of Fiscal Year] )
RETURN 
CALCULATE(
    SELECTEDMEASURE()
    , ALL( 'Calendar' )
    , 'Calendar'[Day of Fiscal Year] <= _currDay
    , 'Calendar'[FY] = _currFiscYr
)

Quarter to Day

QTD = 
var _currFiscQtr = MAX( 'Calendar'[Sequential Fiscal Quarter] )
var _currDay = MAX( 'Calendar'[Day of Fiscal Year] )
RETURN 
CALCULATE(
    SELECTEDMEASURE()
    , ALL( 'Calendar' )
    , 'Calendar'[Day of Fiscal Year] <= _currDay
    , 'Calendar'[Sequential Fiscal Quarter] = _currFiscQtr
)

Month to Day

MTD = 
var _currFiscMth = MAX( 'Calendar'[Sequential Fiscal Month] )
var _currDay = MAX( 'Calendar'[Day of Fiscal Year] )
RETURN 
CALCULATE(
    SELECTEDMEASURE()
    , ALL( 'Calendar' )
    , 'Calendar'[Day of Fiscal Year] <= _currDay
    , 'Calendar'[Sequential Fiscal Month] = _currFiscMth
)

Previous Quarter to Day

PQTD = 
var _currFiscQtr = MAX( 'Calendar'[Sequential Fiscal Quarter] )
var _prevFiscQtr = _currFiscQtr - 1
var _dayOfFiscalQtr = MAX( 'Calendar'[Day of Fiscal Quarter] )
// likely need to errHndl cases where largest day in fiscal qtr doesn't line up right
 
RETURN 
CALCULATE(
    SELECTEDMEASURE()
    , ALL( 'Calendar' )
    , 'Calendar'[Sequential Fiscal Quarter] = _prevFiscQtr
    , 'Calendar'[Day of Fiscal Quarter] <= _dayOfFiscalQtr
)

Quarter Over Quarter to Day

QOQTD = 
var _cQTD =
    var _currFiscQtr = MAX( 'Calendar'[Sequential Fiscal Quarter] )
    var _currDay = MAX( 'Calendar'[Day of Fiscal Year] )
    RETURN 
    CALCULATE(
        SELECTEDMEASURE()
        , ALL( 'Calendar' )
        , 'Calendar'[Day of Fiscal Year] <= _currDay
        , 'Calendar'[Sequential Fiscal Quarter] = _currFiscQtr
    )
var _pQTD =
    var _currFiscQtr = MAX( 'Calendar'[Sequential Fiscal Quarter] )
    var _prevFiscQtr = _currFiscQtr - 1
    var _dayOfFiscalQtr = MAX( 'Calendar'[Day of Fiscal Quarter] )
    // likely need to errHndl cases where largest day in fiscal qtr doesn't line up right
     
    RETURN 
    CALCULATE(
        SELECTEDMEASURE()
        , ALL( 'Calendar' )
        , 'Calendar'[Sequential Fiscal Quarter] = _prevFiscQtr
        , 'Calendar'[Day of Fiscal Quarter] <= _dayOfFiscalQtr
    )
RETURN
    IF (
        NOT ISBLANK ( _cQTD ) && NOT ISBLANK ( _pQTD ),
        _cQTD - _pQTD
    )

Quarter Over Quarter to Day %

QOQTD % = 
var _cQTD =
    var _currFiscQtr = MAX( 'Calendar'[Sequential Fiscal Quarter] )
    var _currDay = MAX( 'Calendar'[Day of Fiscal Year] )
    RETURN 
    CALCULATE(
        SELECTEDMEASURE()
        , ALL( 'Calendar' )
        , 'Calendar'[Day of Fiscal Year] <= _currDay
        , 'Calendar'[Sequential Fiscal Quarter] = _currFiscQtr
    )
var _pQTD =
    var _currFiscQtr = MAX( 'Calendar'[Sequential Fiscal Quarter] )
    var _prevFiscQtr = _currFiscQtr - 1
    var _dayOfFiscalQtr = MAX( 'Calendar'[Day of Fiscal Quarter] )
    // likely need to errHndl cases where largest day in fiscal qtr doesn't line up right
     
    RETURN 
    CALCULATE(
        SELECTEDMEASURE()
        , ALL( 'Calendar' )
        , 'Calendar'[Sequential Fiscal Quarter] = _prevFiscQtr
        , 'Calendar'[Day of Fiscal Quarter] <= _dayOfFiscalQtr
    )
RETURN
    IF (
        NOT ISBLANK ( _cQTD ) && NOT ISBLANK ( _pQTD ),
        DIVIDE( _cQTD - _pQTD , _pQTD )
    )

Last Month

LM = var _currOffset = MAX('Calendar'[Fiscal Month Offset])
var _offsetBegin = MIN('Calendar'[Fiscal Month Offset]) - 1
var _offsetEnd = _currOffset - 1 
var _currYr = MAX('Calendar'[Fiscal Year Offset])
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES('Calendar'[Date]),
    ALL('Calendar'),
    'Calendar'[Fiscal Month Offset] >= _offsetBegin &&
    'Calendar'[Fiscal Month Offset] <= _offsetEnd
)
var _rslt = 
CALCULATE( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    _rslt,
    _currOffset <= 0
)

Previous Period

PP = 
// dynamic to fiscal month, quarter, year
var _monthsBack =
SWITCH(
    TRUE(),
    HASONEVALUE('Calendar'[Fiscal Month Offset]), 1,
    HASONEVALUE('Calendar'[Fiscal Quarter Offset]), 3,
    12
)
var _currOffset = MAX( 'Calendar'[Sequential Fiscal Month] )
var _offsetBegin = MIN( 'Calendar'[Sequential Fiscal Month] ) - _monthsBack
var _offsetEnd = _currOffset - _monthsBack
var _currYr = MAX( 'Calendar'[Fiscal Year Offset] )
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES( 'Calendar'[Date] )
    , ALL( 'Calendar' )
    , 'Calendar'[Sequential Fiscal Month] >= _offsetBegin &&
        'Calendar'[Sequential Fiscal Month] <= _offsetEnd
)
var _rslt = 
CALCULATE ( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    _rslt,
    _currOffset <= 0 || _currYr <= 0
)

Current vs Previous Period

CP vs PP = 
// dynamic to fiscal month, quarter, year
var _monthsBack =
SWITCH(
    TRUE(),
    HASONEVALUE('Calendar'[Fiscal Month Offset]), 1,
    HASONEVALUE('Calendar'[Fiscal Quarter Offset]), 3,
    12
)
var _currOffset = MAX( 'Calendar'[Sequential Fiscal Month] )
var _offsetBegin = MIN( 'Calendar'[Sequential Fiscal Month] ) - _monthsBack
var _offsetEnd = _currOffset - _monthsBack
var _cy = MAX( 'Calendar'[Fiscal Year Offset] )
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES( 'Calendar'[Date] )
    , ALL( 'Calendar' )
    , 'Calendar'[Sequential Fiscal Month] >= _offsetBegin &&
        'Calendar'[Sequential Fiscal Month] <= _offsetEnd
)
var _rslt = 
CALCULATE ( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    SELECTEDMEASURE() - _rslt,
    _cy <= 0 && _rslt
)

Current vs Previous Period %

CP vs PP % = 
// dynamic to fiscal month, quarter, year
var _monthsBack =
SWITCH(
    TRUE(),
    HASONEVALUE('Calendar'[Fiscal Month Offset]), 1,
    HASONEVALUE('Calendar'[Fiscal Quarter Offset]), 3,
    12
)
var _currOffset = MAX( 'Calendar'[Sequential Fiscal Month] )
var _offsetBegin = MIN( 'Calendar'[Sequential Fiscal Month] ) - _monthsBack
var _offsetEnd = _currOffset - _monthsBack
var _cy = MAX( 'Calendar'[Fiscal Year Offset] )
var _datesOffsetPd = 
CALCULATETABLE(
    VALUES( 'Calendar'[Date] )
    , ALL( 'Calendar' )
    , 'Calendar'[Sequential Fiscal Month] >= _offsetBegin &&
        'Calendar'[Sequential Fiscal Month] <= _offsetEnd
)
var _rslt = 
CALCULATE ( 
    SELECTEDMEASURE(), 
    'Calendar'[Date] IN _datesOffsetPd
)
RETURN
DIVIDE(
    DIVIDE( SELECTEDMEASURE() - _rslt, ABS(_rslt) ) ,
    _cy <= 0
)

Example

Amount QTD = 
Calculate( [measure],
KEEPFILTERS('z-CG Time Intelegence'[Time Intelligence Type] = 'QTD' ))
//Where Time Intelligence Type is a calculated group in Model View

Time Since Last Refresh

let`s create a blank query in power query editor and call it zz-ReportRefresh.

Example

let

    Source = #table({"Dataset Refresh DT - UTC"},{{DateTimeZone.FixedUtcNow()}}),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dataset Refresh DT - UTC", type datetimezone}}),

    #"Inserted Local Time" = Table.AddColumn(#"Changed Type", "Dataset Refresh DT - EST", each DateTimeZone.SwitchZone([#"Dataset Refresh DT - UTC"],-4), type datetimezone)

in

    #"Inserted Local Time"

let`s create a measure and call it Time Since Last Refresh.

Time Since Last Refresh =
var _currTm = UTCNOW()

var _refreshTm = MAX( 'zz-ReportRefresh'[Dataset Refresh DT - UTC] )

var _delta = ROUNDDOWN ( DATEDIFF(_refreshTm, _currTm, MINUTE ) / 60 , 0 )

var _isMultiple = _delta > 1

var _is0 = _delta = 0

return

 

SWITCH(

    TRUE(),

    _isMultiple, FORMAT( _delta, "0" ) & " hours since last dataset refresh",

    NOT _is0, FORMAT( _delta, "0") & " hour since last dataset refresh",

    _is0, "Dataset refreshed in the past hour"

)

The Last Date by Each Groups

let's create a blank query in power query editor and set the Source from existing query, in this case, Source = Inventory_wo_rank. As a second step, we should remove the all the unnecessary columns #"Removed Columns" and remove all the duplicates #"Removed Duplicates".

Example

let
    
    Source = Inventory_wo_rank,
    #"Removed Columns" = Table.RemoveColumns(Source ,{"Fiscal Week Desc", "Super Region", "Region", "Country Name", "Plant & Name", "CFN", 
    "Material", "Fiscal Year", "Fiscal Week Num", "Month", "Day",  "Curreny", "FY24 AOP", "FY25 AOP"}),
     
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Source"}, {{"All", each _, type table [Source=nullable text, FYandFW0num=nullable number]}}),
    Custom1 = Table.TransformColumns( #"Grouped Rows", {{"All", each  Table.AddRankColumn( _, "rank", {{"FYandFW0num", Order.Descending}} )}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"FYandFW0num", "rank"}, {"FYandFW0num", "rank"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{ {"Source", type text}, {"FYandFW0num", Int64.Type}, {"rank", Int64.Type}})
in
     #"Changed Type1"
    

In Power Query we expect to see, at this point, similar to table below.

\[\begin{array}{|l|c|} \hline \text{Source} & \text{FYandFW0num} \\ \hline Group A & \text{202448} \\ Group A & \text{202451} \\ Group B & \text{202501} \\ Group B & \text{202452} \\ Group B & \text{202453} \\ Group C & \text{202452} \\ \hline \end{array}\]

    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Source"}, {{"All", each _, type table [Source=nullable text, FYandFW0num=nullable number]}}),
    Custom1 = Table.TransformColumns( #"Grouped Rows", {{"All", each  Table.AddRankColumn( _, "Rank", {{"FYandFW0num", Order.Descending}} )}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"FYandFW0num", "Rank"}, {"FYandFW0num", "Rank"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{ {"Source", type text}, {"FYandFW0num", Int64.Type}, {"Rank", Int64.Type}})
in
     #"Changed Type1"

Finally, we get the ranking within each Group.

\[\begin{array}{|l|c|c|} \hline \text{Source} & \text{FYandFW0num} & \text{Rank} \\ \hline Group A & \text{202448} & \text{2} \\ Group A & \text{202451} & \text{1} \\ Group B & \text{202501} & \text{1} \\ Group B & \text{202452} & \text{2} \\ Group B & \text{202453} & \text{3} \\ Group C & \text{202452} & \text{1} \\ \hline \end{array}\]

Current week

\[\begin{array}{|l|c|} \hline \text{FYandFW0num} & \text{Rank} \\ \hline \text{202448} & \text{1} \\ \text{202451} & \text{2} \\ \text{202452} & \text{3} \\ \text{202453} & \text{4} \\ \text{202501} & \text{5} \\ \hline \end{array}\]

CW Amt = 
VAR CurrentFiscalWeek = MAX( 'Inventory'[FYandFW0num] )
RETURN
CALCULATE( [Inventory],
           'Inventory'[FYandFW0num] = CurrentFiscalWeek )
           
PW Amt = 

CALCULATE( [Inventory],
           KEEPFILTERS( 'Inventory'[toRank2.Rank] = MIN('Inventory'[toRank2.Rank]) )           

The Last Two Weeks Change

Example

lets set the data model first. If the dates would be FY24 WK52 then we should have rank the given format and use the same methodology below.

CurrWeek = 
VAR TAB =
    TOPN(
        2,
        'Week Slice',
        'Week Slice'[Week],
        DESC
    )
VAR _MAX = MAXX(TAB, 'Week Slice'[Week])
RETURN
_MAX

PrevWeek = 
VAR TAB =
    TOPN(
        2,
        'Week Slice',
        'Week Slice'[Week],
        DESC
    )
VAR _MIN = MINX(TAB, 'Week Slice'[Week])
RETURN
_MIN

current Inventory = 
VAR _MAX = VALUE([CurrWeek])
VAR TotalCW =
CALCULATE(
    [Total],
    ALL(Week),
    Week[Week] = _MAX
)
RETURN
TotalCW

Total = sum('Fact'[Amount])

Change = 
VAR _MAX = VALUE([CurrWeek])
VAR _MIN = VALUE([PrevWeek])
VAR TotalCW =
CALCULATE(
    [Total],
    ALL(Week),
    Week[Week] = _MAX
)
VAR TotalPW =
CALCULATE(
    [Total],
    ALL(Week),
    Week[Week] = _MIN
)
VAR Final =
TotalCW - TotalPW
RETURN
Final

Comparison = "Change from week "&[PrevWeek] &" to "&[CurrWeek]

Viz Ideas

Book Marks Example

Tool Tips Example

DimCalendar

RLS

Rate Conversion

dimFX

Data Modeling