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

Following link may give an idea of how to set up RLS. Yet, I will show you a better way.

Fact_RLS

Fact_RLS dataflow as:

\[\begin{array}{|l|c|c|} \hline \text{User} & \text{Email Address} & \text{Access} & \text{Region}& \text{RPT ID}& \text{Access Status} \\ \hline User1 & \text{user1@email.com} &\text{Admin} &\text{} &\text{} &\text{Access Assigned} \\ User2 & \text{user2@email.com} &\text{Admin} &\text{} &\text{} &\text{Access Assigned} \\ User3 & \text{user3@email.com} &\text{Region} &\text{Americas} &\text{} &\text{Access Assigned} \\ User4 & \text{user4@email.com} &\text{Region} &\text{Asia Pasific} &\text{} &\text{Access Assigned} \\ User5 & \text{user5@email.com} &\text{Region} &\text{EMEA} &\text{} &\text{Access Assigned} \\ User6 & \text{user6@email.com} &\text{Region} &\text{GOCS} &\text{} &\text{Access Assigned} \\ User7 & \text{user7@email.com} &\text{Region} &\text{Greater China} &\text{} &\text{Access Assigned} \\ User8 & \text{user8@email.com} &\text{Region} &\text{Greater China} &\text{} &\text{Access Assigned} \\ User9 & \text{user9@email.com} &\text{RPT ID} &\text{} &\text{USA} &\text{Access Assigned} \\ User10 & \text{user10@email.com} &\text{RPT ID} &\text{} &\text{TUK} &\text{Access Assigned} \\ \hline \end{array}\]

From the Modeling tab, select Manage Roles and add a new role as follow. For ADMIN role set up NO FILTER.

Keep in mine, the Fact_RLS dataflow has no relationship with other tables in the data modelling. Yet dimReporting has relationship with other tables. For other Roles set up the filter as follows;

BUSINESS PARTNER

[Finance Business Partner] #A column name in the Selected Table (let's assume it is dimReporting table)
    IN CALCULATETABLE (
        VALUES ( 'Fact_RLS'[User] ),
                 'Fact_RLS'[Email Address]
        = USERNAME ()
    )

FINANCIAL ANALYST

[Analyst] #A column name in the Selected Table (let's assume it is dimReporting table)
    IN CALCULATETABLE (
        VALUES ( 'Fact_RLS'[User] ),
                 'Fact_RLS'[Email Address]
        = USERNAME ()
    )

RPT ID

[Reporting Location ID] #A column name in the Selected Table (let's assume it is dimReporting table)
    IN CALCULATETABLE (
        VALUES ( 'Fact_RLS'[RPT ID] ),
                 'Fact_RLS'[Email Address]
        = USERNAME ()
    )

SENIOR LEADERSHIP

[Senior Leadership Team] #A column name in the Selected Table (let's assume it is dimReporting table)
    IN CALCULATETABLE (
        VALUES ( Fact_RLS[User] ),
                 Fact_RLS[Email Address]
        = USERNAME ()
    )

Currency Rate Conversion

Fact_FX

Fact_FX dataflow as:

\[\begin{array}{|l|l|c|l|l|l|l|l|l|} \hline \text{Currency Code} & \text{Country} & \text{Conversion Method} & \text{Attribute} & \text{FX Rate}& \text{FX Scenario}& \text{FX Type}& \text{Date} \\ \hline \text{USD} & \text{United States} & \text{USD per LC} & \text{FY25 AOP} & \text{1} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{USD} & \text{United States} & \text{USD per LC} & \text{FY25 May M-BS} & \text{1} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{USD} & \text{United States} & \text{USD per LC} & \text{FY25 May M-PL} & \text{1} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{EUR} & \text{Europe} & \text{USD per LC} & \text{FY25 AOP} & \text{1.05} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{EUR} & \text{Europe} & \text{USD per LC} & \text{FY25 May M-BS} & \text{1.085198971} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{EUR} & \text{Europe} & \text{USD per LC} & \text{FY25 May M-PL} & \text{1.070354394} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{JPY} & \text{Japan} & \text{USD per LC} & \text{FY25 AOP} & \text{0.006896552} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{JPY} & \text{Japan} & \text{USD per LC} & \text{FY25 May M-BS} & \text{0.006401844} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{JPY} & \text{Japan} & \text{USD per LC} & \text{FY25 May M-PL} & \text{0.006460157} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{GBP} & \text{United Kingdom} & \text{USD per LC} & \text{FY25 AOP} & \text{1.27} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{GBP} & \text{United Kingdom} & \text{USD per LC} & \text{FY25 May M-BS} & \text{1.271051795} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{GBP} & \text{United Kingdom} & \text{USD per LC} & \text{FY25 May M-PL} & \text{1.244895927} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{CHF} & \text{Switzerland} & \text{USD per LC} & \text{FY25 AOP} & \text{1.111111111} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{CHF} & \text{Switzerland} & \text{USD per LC} & \text{FY25 May M-BS} & \text{1.09751413} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{CHF} & \text{Switzerland} & \text{USD per LC} & \text{FY25 May M-PL} & \text{1.096791884} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{CAD} & \text{Canada} & \text{USD per LC} & \text{FY25 AOP} & \text{0.735294118} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{CAD} & \text{Canada} & \text{USD per LC} & \text{FY25 May M-BS} & \text{0.732412934} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{CAD} & \text{Canada} & \text{USD per LC} & \text{FY25 May M-PL} & \text{0.732010834} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{AUD} & \text{Australia} & \text{USD per LC} & \text{FY25 AOP} & \text{0.675} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{AUD} & \text{Australia} & \text{USD per LC} & \text{FY25 May M-BS} & \text{0.666600007} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{AUD} & \text{Australia} & \text{USD per LC} & \text{FY25 May M-PL} & \text{0.648701947} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \text{AED} & \text{United Arab} & \text{USD per LC} & \text{FY25 AOP} & \text{0.272257011} & \text{AOP} & \text{AOP} & \text{5/15/2024}\\ \text{AED} & \text{United Arab} & \text{USD per LC} & \text{FY25 May M-BS} & \text{0.272264423} & \text{Actual} & \text{BS} & \text{5/15/2024}\\ \text{AED} & \text{United Arab} & \text{USD per LC} & \text{FY25 May M-PL} & \text{0.272286663} & \text{Actual} & \text{PL} & \text{5/15/2024}\\ \hline \end{array}\]

dimCurrency

dimCurrency dataflow as:

\[\begin{array}{|l|c|} \hline \text{Currency Code} & \text{Country} \\ \hline \text{USD} & \text{United States} \\ \text{EUR} & \text{Europe} \\ \text{JPY} & \text{Japan} \\ \text{GBP} & \text{United Kingdom} \\ \text{CHF} & \text{Switzerland} \\ \text{CAD} & \text{Canada} \\ \text{AUD} & \text{Australia} \\ \text{AED} & \text{United Arab} \\ \text{CNY} & \text{China} \\ \hline \end{array}\]

Fact_AOP

Fact_AOP dataflow as:

\[\begin{array}{|l|l|} \hline \text{Data Source} & \text{RPT ID} & \text{G/L Account}& \text{Amount}& \text{Date}& \text{Currency}& \text{Amount Type}& \text{Scenario}& \text{Product ID} \\ \hline \text{filename.xlsx} & \text{TMX} & \text{5340900} & \text{11538} & \text{5/15/2024} & \text{USD} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{BEM} & \text{5265000} & \text{347659} & \text{5/15/2024} & \text{USD} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{BEM} & \text{5280000} & \text{106681} & \text{5/15/2024} & \text{USD} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{BEM} & \text{5210900} & \text{682041} & \text{5/15/2024} & \text{EUR} & \text{Currency} & \text{AOP} & \text{P123}\\ \text{filename.xlsx} & \text{BEM} & \text{5225000} & \text{796059} & \text{5/15/2024} & \text{EUR} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{BHP} & \text{5265000} & \text{17520} & \text{5/15/2024} & \text{EUR} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{BHP} & \text{5280000} & \text{47083} & \text{5/15/2024} & \text{EUR} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{BHP} & \text{5260003} & \text{24504} & \text{5/15/2024} & \text{EUR} & \text{Currency} & \text{AOP} & \text{P127}\\ \text{filename.xlsx} & \text{BHP} & \text{5210900} & \text{86324} & \text{5/15/2024} & \text{EUR} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{BHP} & \text{5225000} & \text{152606} & \text{5/15/2024} & \text{JPY} & \text{Currency} & \text{AOP} & \text{P129}\\ \text{filename.xlsx} & \text{BHP} & \text{5010900} & \text{30000} & \text{5/15/2024} & \text{JPY} & \text{Currency} & \text{AOP} & \text{P130}\\ \text{filename.xlsx} & \text{BHP} & \text{5110900} & \text{60000} & \text{5/15/2024} & \text{JPY} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{BHP} & \text{5004000} & \text{70000} & \text{5/15/2024} & \text{JPY} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{BLS} & \text{5265000} & \text{95389} & \text{5/15/2024} & \text{JPY} & \text{Currency} & \text{AOP} & \text{P123}\\ \text{filename.xlsx} & \text{BLS} & \text{5260003} & \text{27230} & \text{5/15/2024} & \text{JPY} & \text{Currency} & \text{AOP} & \text{P134}\\ \text{filename.xlsx} & \text{BLS} & \text{5210900} & \text{51732} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{BLS} & \text{5225000} & \text{114307} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P136}\\ \text{filename.xlsx} & \text{BLS} & \text{5110900} & \text{22460} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{CMG} & \text{5265000} & \text{185465} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P123}\\ \text{filename.xlsx} & \text{CMG} & \text{5280000} & \text{114718} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P139}\\ \text{filename.xlsx} & \text{CMG} & \text{5260003} & \text{369725} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{CMG} & \text{5210900} & \text{339158} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P141}\\ \text{filename.xlsx} & \text{CMG} & \text{5225000} & \text{819388} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{CMG} & \text{5110900} & \text{10000} & \text{5/15/2024} & \text{GBP} & \text{Currency} & \text{AOP} & \text{P143}\\ \text{filename.xlsx} & \text{EHW} & \text{5350000} & \text{94203} & \text{5/15/2024} & \text{CHF} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{EHW} & \text{5340900} & \text{28086} & \text{5/15/2024} & \text{CHF} & \text{Currency} & \text{AOP} & \text{P121}\\ \text{filename.xlsx} & \text{TBE} & \text{5340900} & \text{11517} & \text{5/15/2024} & \text{CAD} & \text{Currency} & \text{AOP} & \text{P123}\\ \text{filename.xlsx} & \text{TFR} & \text{5340900} & \text{40110} & \text{5/15/2024} & \text{CAD} & \text{Currency} & \text{AOP} & \text{P120}\\ \text{filename.xlsx} & \text{TIL} & \text{5340900} & \text{244978} & \text{5/15/2024} & \text{CAD} & \text{Currency} & \text{AOP} & \text{P123}\\ \hline \end{array}\]

Data Modeling

USD Currency Conversion

Base Measure = SUM('Fact_AOP'[Amount])



Sum@AOP =
VAR _AggregatedAmountInCurrency =
    ADDCOLUMNS (
        SUMMARIZE (
            'Fact_AOP',
            'Calendar'[Date],
            'dimCurrency'[Currency Code]
        ),
        "@AmountInCurrency", [Base Measure],
        "@Rate", CALCULATE(
                    MAX( 'factFX'[FX Rate] ),
                    KEEPFILTERS( 'factFX'[FX Type] = "AOP" ),
                    ALLEXCEPT('Calendar', 'Calendar'[Date])
                )
    )
VAR _Result =
    SUMX (
        _AggregatedAmountInCurrency,
        [@AmountInCurrency] * COALESCE( [@Rate], IF( 'dimCurrency'[Currency Code] = "USD" , 1) )
    )
RETURN
    _Result

Data Modeling