Business Intelligence | Dutch Data Dude

Updated: Annual radio countdown Top 2000 in Power BI

on January 3, 2019

This is starting to become a tradition, sort of. My Power BI analysis of the Top 2000 has been updated to include 2018’s edition!

Read all about it in the original post from 2016.

Full screen

Enjoy and happy holidays! See you next year.

Farewell Tour

on September 18, 2018

by Jeroen ter Heerdt 0 Comments

DUTCH DATA DUDE
FAREWELL TOUR

A CHANGE IS GONNA COME
Na bijna 10 jaar waag ik de grote stap. Ik verhuis naar Amerika en ga voor Microsoft in Seattle aan de slag. Daarmee komt er een einde aan mijn werk als Dutch Data Dude in Nederland (en daarbuiten). Werk dat ik met veel passie heb gedaan en heb kunnen doen dankzij en met jullie. Om dit deel gepast af te sluiten doe ik een FAREWELL TOUR: ik klim nog een laatste keer op het podium. Bij jullie, voor jullie. Want jullie hebben mij in staat gesteld te worden wie ik wilde zijn: het geluid van Microsoft rondom data en Artificial Intelligence. Dutch Data Dude signs off.

DE TOUR

Tijdens de FAREWELL TOUR geef ik in korte tijd een flink aantal presentaties.
Dit organiseer ik samen met partners in October / November 2018. Het onderwerp van de presentaties is Business Intelligence, Big Data, Machine Learning of Artificial Intelligence. Soms staat mijn presentatie op zich, soms is het onderdeel van een groter geheel. Alles in samenspraak met de organiserende partner. Ik hoop je te mogen begroeten tijdens de FAREWELL TOUR.

DE LOCATIES

Hieronder staan de locaties die ik aandoe tijdens mijn FAREWELL TOUR. Voor publieke evenementen kun je je direct inschrijven via de aangegeven link.

Datum	Tijd	Plaats	Organisatie	Soort evenement	Inschrijven
17 October	10:00	Bussum	CraniumConnect	Voor leden	Website
17 October	17:00	Den Haag	TopBI	Intern	n.v.t.
18 October	17:00	Amsterdam	QNH	Intern	n.v.t.
23 October	16:00	Amsterdam	Datafied	Publiek	Inschrijven
25 October	18:00	Rotterdam	Motion10	Publiek	Inschrijven
29 October	16:30	Eindhoven	Valid	Publiek	Inschrijven
30 October	13:30	Veenendaal	HSO	Op uitnodiging	Website
30 October	16:00	Huizen	Sigma Data Consulting	Intern	n.v.t.
31 October	17:00	Vianen	Rubicon	Publiek	Inschrijven
2 November	09:00	Amsterdam	Breinwave	Op uitnodiging	n.v.t.
6 November	13:30	Veenendaal	Hillstar	Publiek	Inschrijven
7 November	19:00	Veenendaal	Infosupport	Publiek (beperkt aantal plaatsen)	Inschrijven
8 November	14:30	Hoofddorp	Macaw	Publiek	Inschrijven
12 November	13:30	Utrecht	Axians	Intern	n.v.t.
13 November	17:30	Utrecht	Caesar Experts	Publiek	Inschrijven
14 November	10:00	Bussum	CraniumConnect	Voor leden	Website
14 November	18:00	Vianen	Sogeti	Intern	n.v.t.
15 November	15:00	Amsterdam	Ireckonu	Op uitnodiging	n.v.t.
29 November	17:00	Den Bosch	Power BI Gebruikersgroep	Publiek	Inschrijven

Power BI Connector for CBS Open Data / Statline updated

on July 10, 2018

Business Intelligence

by Jeroen ter Heerdt 0 Comments

I have updated the instructions and the code for the Custom Connector for CBS Open Data / Statline. Please see the original post:

Power BI Connector for CBS Open Data / Statline

Ultimate Time Based Calculations Cheat Sheet for DAX / Power BI (including Week based calculations)

on April 6, 2018

Business Intelligence

by Jeroen ter Heerdt 2 Comments

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

Mapping Dutch geographical data using Power BI

on December 28, 2017

Business Intelligence

by Jeroen ter Heerdt 0 Comments

I have written before on the subject of using Power BIs mapping capabilities (see the post on ArcGIS) and using the maps with Dutch geographical data (see using Dutch postal codes in Power Map). However, I have never did a more thorough exploration of the mapping capabilities of Power BI using Dutch data. Until now! (Niels, thanks for requesting me to do this).

Please see the Power BI report below – the first tab shows the dataset I used. The other tabs show the way(s) to work with the various maps to plot Dutch data. Unfortunately the ArcGIS visual is not supported in the mode I use here – please download the original Power BI desktop file below. Enjoy!

Download Power BI Desktop file

Updated: Annual radio countdown Top 2000 in Power BI

on December 21, 2017

by Jeroen ter Heerdt 4 Comments

Oops, I did it again. It’s that time of the year. A day later than last year (sorry about that ;))….

Back by popular demand: the Top 2000 visualized in Power BI. Read all about it in last year’s post.

Full screen

Enjoy and happy holidays! See you next year.

SQL Server 2017: It’s here!

on October 2, 2017

by Jeroen ter Heerdt 0 Comments

Anxiously you have waited for this day. The day you could finally get your hands on the latest version of the best database platform available. Today is that day. SQL Server 2017 is available and it runs on Windows, Linux and Docker. Find more info here: https://www.microsoft.com/en-us/sql-server/sql-server-2017. What changed? So many things it is hard to mention them all. Have a look at the Ignite sessions to get a feel for what happened.

Power BI Connector for CBS Open Data / Statline

on July 18, 2017

Business Intelligence

by Jeroen ter Heerdt 3 Comments

Updated 7/10/2018: path updated below and new file on GitHub.

Since this post primarily concerns a Dutch data source, the rest of this post will be in Dutch. English summary below.

De open data die het Centraal Bureau voor de Statistiek (CBS) via Statline (http://statline.cbs.nl) aanlevert is van onschatbare waarde voor analisten. Over diverse thema’s wordt informatie gepubliceerd, zoals woning, demografie, inkomen en arbeid. Erg handig om je verkopen per provincie te vergelijken met de samenstelling van de bevolking bijvoorbeeld. De juiste informatie uit Statline halen was erg lastig, maar dankzij de Power BI Connector voor CBS Open Data / Statline is dat niet meer het geval.

Gebruik

Wanneer je de connector hebt geïnstalleerd (zie beneden) vind je de connector in de lijst met data bronnen in Power BI Desktop:

Klik op CBSOpenData en klik Connect. Vul één of meerdere zoekwoorden in (bijvoorbeeld arbeid) en klik OK:

De connector communiceert met de CBS Statline website en levert de thema’s en eventueel bijbehorende tabellen. Dit kan een tijdje duren, afhankelijk van het aantal thema’s en tabellen die horen bij de woorden die je ingevoerd hebt. Kies één of meerdere tabellen en je kunt ze laten of bewerken! Eenvoudiger wordt het niet!

Installatie

Omdat de custom connectors nog erg nieuw zijn is er op dit moment een omweg nodig om ze te kunnen gebruiken in Power BI Desktop. Volg deze stappen:

Zet de Custom data connectors preview feature aan binnen Power BI Desktop via File à Options:
Sluit Power BI Desktop.
Lees de documentatie op Github om te zien welke directory je moet maken om Power BI de custom connectors te laten laden. Op dit moment is dat [My Documents]\Power BI Desktop\Custom Connectors.
Download het .mez bestand van de Github van de CBS Open Data connector en sla het bestand op in de directory die je net gemaakt hebt. Je kunt natuurlijk ook de source code helemaal bekijken.
Start Power BI Desktop op. Als het goed is staat de connector nu onder ‘Other’ in de lijst (zie screenshot bovenaan in deze post). De snelheid van ophalen is niet hoog, omdat er gebruik wordt gemaakt van de publieke API, maar toch, met wat geduld moet het werken. Veel succes!

English

The CBS Open Data / Statline website (http://statline.cbs.nl) is a very popular source of all types of statistical information about the Netherlands, provided by the government. In order to use this connector in Power BI, download the .mez file from Github and save the file in your custom connectors directory. Please see the documentation to find which directory to use. Enable the Custom Connectors preview feature in Power BI Desktop, restart Power BI and the connector should show up. You can of course also look at the source code on Github. Enjoy!

Implementing incremental data load using Azure Data Factory

on March 22, 2017

by Jeroen ter Heerdt 2 Comments

Azure Data Factory is a fully managed data processing solution offered in Azure. It connects to many sources, both in the cloud as well as on-premises. One of the basic tasks it can do is copying data over from one source to another – for example from a table in Azure Table Storage to an Azure SQL Database table. To get the best performance and avoid unwanted duplicates in the target table, we need to include incremental data load or delta’s. Also, we can build mechanisms to further avoid unwanted duplicates when a data pipeline is restarted.

In this post I will explain how to cover both scenario’s using a pipeline that takes data from Azure Table Storage, copies it over into Azure SQL and finally brings a subset of the columns over to another Azure SQL table. The result looks like this:

The components are as follows:

MyAzureTable: the source table in Azure Table Storage
CopyFromAzureTableToSQL: the pipeline copying data over into the first SQL table
Orders: the first SQL Azure database table
CopyFromAzureSQLOrdersToAzureSQLOrders2: the pipeline copying data from the first SQL table to the second – leaving behind certain columns
Orders2: the second and last SQL Azure database table

Setting up the basics is relatively easy. The devil is in the details, however.

The linked services

Every data pipeline in Azure Data Factory begins with setting up linked services. In this case, we need two; one to the Azure Table storage and one to SQL Azure. The definition of the linked service to Azure Table Storage is as follows:

{
    "name": "MyAzureStorage",
    "properties": {
        "description": "",
        "hubName": "my_hub",
        "type": "AzureStorage",
        "typeProperties": {
            "connectionString": "DefaultEndpointsProtocol=https;AccountName=[yourstorageaccountname];AccountKey=[youraccountkey]"
        }
    }
}

{

"name": "MyAzureStorage",

"properties": {

"description": "",

"hubName": "my_hub",

"type": "AzureStorage",

"typeProperties": {

"connectionString": "DefaultEndpointsProtocol=https;AccountName=[yourstorageaccountname];AccountKey=[youraccountkey]"

}

The SQL Azure linked service definition looks like this:

{
    "name": "MyAzureSQL",
    "properties": {
        "description": "",
        "hubName": "my_hub",
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Data Source=tcp:[yourdatabase].database.windows.net,1433;Initial Catalog=[yourdatabase];Integrated Security=False;User ID=[youruser]@[yourdatabase];Password=[yourpassword];Connect Timeout=30;Encrypt=True"
        }
    }
}

{

"name": "MyAzureSQL",

"properties": {

"description": "",

"hubName": "my_hub",

"type": "AzureSqlDatabase",

"typeProperties": {

"connectionString": "Data Source=tcp:[yourdatabase].database.windows.net,1433;Initial Catalog=[yourdatabase];Integrated Security=False;User ID=[youruser]@[yourdatabase];Password=[yourpassword];Connect Timeout=30;Encrypt=True"

}

Note the name property – we will need to refer to it later.

The datasets

Datasets define tables or queries that return data that we will process in the pipeline. The first dataset we need to define is the source dataset (called MyAzureTable). The definition is as follows:

{
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",
    "name": "MyAzureTable",
    "properties": {
        "type": "AzureTable",
        "typeProperties": {
            "tableName": "[yourtablename]"
        },
        "linkedServiceName": "MyAzureStorage",
        "availability": {
            "frequency": "Hour",
            "interval": 1
        },
        "external":true,
        "policy": { }
    }
}

{

"$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",

"name": "MyAzureTable",

"properties": {

"type": "AzureTable",

"typeProperties": {

"tableName": "[yourtablename]"

"linkedServiceName": "MyAzureStorage",

"availability": {

"frequency": "Hour",

"interval": 1

"external":true,

"policy": { }

}

Note that, again, this item has a name. We will use it in the pipeline later. Also, you will need to specify the name of your Azure Table in the “tablename” property. Note that the “LinkedServiceName” property is set to the name of the linked service we definied earlier. This way, Azure Data Factory knows where to find the table. Also, the “availability” property specifies the slices Azure Data Factory uses to process the data. This defines how long ADF waits before processing the data as it waits for the specified time to pass before processing. The settings above specify hourly slices, which means that data will be processed every hour. We will later set up the pipeline in such a way that ADF will just process the data that was added or changed in that hour, not all data available (as is the default behavior). Minimum slice size currently is 15 minutes. Also note that the dataset is specified as being external (“external”:true). This means that ADF will not try to coördinate tasks for this table as assumes the data will be written from somewhere outside ADF (your application for example) and will be ready for pickup when the slice size is passed.

The target dataset in SQL Azure follows the same definition:

{
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",
    "name": "Orders",
    "properties": {
        "type": "AzureSqlTable",
        "linkedServiceName": "MyAzureSQL",
        "structure": [
        {
            "name":"Timstamp",
            "type":"DateTimeOffset"
        },
        {
            "name":"OrderNr",
            "type":"String"
        },
        {
            "name":"SalesAmount",
            "type":"Decimal"
        },
        {
            "name":"ColumnForADFuseOnly",
            "type":"Byte[]"
        },
        {
            "name":"OrderTimestamp",
            "type":"DateTimeOffset"
        }
        ],
        "typeProperties": {
            "tableName": "[dbo].[Orders]"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        }
    }
}

{

"$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",

"name": "Orders",

"properties": {

"type": "AzureSqlTable",

"linkedServiceName": "MyAzureSQL",

"structure": [

{

"name":"Timstamp",

"type":"DateTimeOffset"

{

"name":"OrderNr",

"type":"String"

{

"name":"SalesAmount",

"type":"Decimal"

{

"name":"ColumnForADFuseOnly",

"type":"Byte[]"

{

"name":"OrderTimestamp",

"type":"DateTimeOffset"

}

"typeProperties": {

"tableName": "[dbo].[Orders]"

"availability": {

"frequency": "Hour",

"interval": 1

}

Important to note is that we defined the structure explicitly – it is not required for the working of the first pipeline, but it is for the second, which will use this same table as source. Also note that presence of the column ‘ColumnForADuseOnly’ in the table. This column is later used by ADF to make sure data that is already processed is not again appended to the target table. Of course, the SQL table itself will need to have (at least) the same columns and matching data types:

The first pipeline (from Azure Table to SQL)

The first pipeline takes the order data in the Azure table and copies it into the Orders table in SQL Azure. It does that incrementally and with repeatability – which means that a) each slice will only process a specific subset of the data and b) if a slice is restarted the same data will not be copied over twice. This results in a fast processing engine without duplication in the target table – data is copied over once, regardless of the number of restarts. Note that by default ADF copies all data over to the target so you would get so many rows in the table as there are orders in the Azure Table times the number of slices that ran (each slice bringing over the full Azure table). The definition is as follows:

{
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Pipeline.json",
    "name": "CopyFromAzureTabletoSQL",
    "properties": {
        "description": "Copies data incrementally from Azure Table Orders to Azure SQL Orders table",
        "activities": [
            {
                "name": "CopyActivityTemplate",
                "type": "Copy",
                "inputs": [
                    {
                        "name": "MyAzureTable"
                    }
                ],
                "outputs": [
                    {
                        "name": "Orders"
                    }
                ],
                "typeProperties": {
                    "source": {
                        "type": "AzureTableSource",
                        "azureTableSourceQuery": "$$Text.Format('OrderTimestamp ge datetime\\'{0:yyyy-MM-ddTHH:mm:ssZ}\\' and OrderTimestamp lt datetime\\'{1:yyyy-MM-ddTHH:mm:ssZ}\\'', SliceStart,SliceEnd)"
                    },
                    "sink": {
                        "type": "SqlSink",
                        "sliceIdentifierColumnName": "ColumnForADFuseOnly"
                    }
                },
                "policy": {
                    "concurrency": 10,
                    "executionPriorityOrder": "OldestFirst",
                    "retry": 3,
                    "timeout": "01:00:00"
                },
                "scheduler": {
                    "frequency": "Hour",
                    "interval": 1
                }
            }
        ],
        "start": "2017-03-20T09:00:00Z",
        "end": "2017-03-22T09:00:00Z"
    }
}

{

"$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Pipeline.json",

"name": "CopyFromAzureTabletoSQL",

"properties": {

"description": "Copies data incrementally from Azure Table Orders to Azure SQL Orders table",

"activities": [

{

"name": "CopyActivityTemplate",

"type": "Copy",

"inputs": [

{

"name": "MyAzureTable"

}

"outputs": [

{

"name": "Orders"

}

"typeProperties": {

"source": {

"type": "AzureTableSource",

"azureTableSourceQuery": "$$Text.Format('OrderTimestamp ge datetime\\'{0:yyyy-MM-ddTHH:mm:ssZ}\\' and OrderTimestamp lt datetime\\'{1:yyyy-MM-ddTHH:mm:ssZ}\\'', SliceStart,SliceEnd)"

"sink": {

"type": "SqlSink",

"sliceIdentifierColumnName": "ColumnForADFuseOnly"

}

"policy": {

"concurrency": 10,

"executionPriorityOrder": "OldestFirst",

"retry": 3,

"timeout": "01:00:00"

"scheduler": {

"frequency": "Hour",

"interval": 1

}

"start": "2017-03-20T09:00:00Z",

"end": "2017-03-22T09:00:00Z"

}

Note that the pipeline consists of a single activity, which is a Copy activity. I could have specified another activity in the same pipeline – I have not done so for simplicity. The Copy activity takes as input the Azure Table (MyAzureTable) and outputs into the SQL Azure Table “Orders”. The source Query is very important – as this is used to select just the data we want! We use the column ‘OrderTimestamp’ which and select only the orders from MyAzureTable where the OrderTimestamp is greater than or equal to the starting time of the slice and less than the end time of the slice. A sample query against the Azure Table executed in this way looks like this:

OrderTimestamp ge datetime’2017-03-20T13:00:00Z’ and OrderTimestamp lt datetime’2017-03-20T15:00:00Z’

Also, look at the specification of the “sliceIdentifierColumnName” property on the target (sink) – this column is in the target SQL Azure table and is used by ADF to keep track of what data is already copied over so if the slice is restarted the same data is not copied over twice.

This pipeline will run each hour (“scheduler” properties), starting at 09:00:00 local clock (“specified by the “start” property) and can run 10 slices in parallel (specified by the “concurrency” property).

The second pipeline (from SQL to SQL)

The second pipeline is there to prove the mapping of specific columns to others as well as showing how to do an incremental load from SQL Azure to another target. Note that I use the same linked service so this exercise is not really useful – the same effect could be retrieved by creating a view. The definition is as follows:

{
    "name": "CopyFromAzureSQLOrdersToAzureSQLOrders2",
    "properties": {
        "activities": [
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "SqlSource",
                        "sqlReaderQuery": "$$Text.Format('select * from [dbo].[Orders] where [OrderTimestamp] >= \\'{0:yyyy-MM-dd HH:mm}\\' AND [OrderTimestamp] < \\'{1:yyyy-MM-dd HH:mm}\\'', WindowStart, WindowEnd)"
                    },
                    "sink": {
                        "type": "SqlSink",
                        "sliceIdentifierColumnName": "ColumnForADFuseOnly2",
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    },
                    "translator": {
                        "type": "TabularTranslator",
                        "columnMappings": "SalesAmount:SalesAmount,OrderTimestamp:OrderTimestamp"
                    },
                    "parallelCopies": 10
                },
                "inputs": [
                    {
                        "name": "Orders"
                    }
                ],
                "outputs": [
                    {
                        "name": "Orders2"
                    }
                ],
                "policy": {
                    "timeout": "1.00:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 3,
                    "longRetry": 0,
                    "longRetryInterval": "00:00:00"
                },
                "scheduler": {
                    "frequency": "Hour",
                    "interval": 1
                },
                "name": "Activity-0-[dbo]_[Orders]->[dbo]_[Orders2]"
            }
        ],
        "start": "2017-03-21T10:45:38.999Z",
        "end": "2099-12-30T23:00:00Z",
        "isPaused": false,
        "hubName": "my_hub",
        "pipelineMode": "Scheduled"
    }
}

{

"name": "CopyFromAzureSQLOrdersToAzureSQLOrders2",

"properties": {

"activities": [

{

"type": "Copy",

"typeProperties": {

"source": {

"type": "SqlSource",

"sqlReaderQuery": "$$Text.Format('select * from [dbo].[Orders] where [OrderTimestamp] >= \\'{0:yyyy-MM-dd HH:mm}\\' AND [OrderTimestamp] < \\'{1:yyyy-MM-dd HH:mm}\\'', WindowStart, WindowEnd)"

"sink": {

"type": "SqlSink",

"sliceIdentifierColumnName": "ColumnForADFuseOnly2",

"writeBatchSize": 0,

"writeBatchTimeout": "00:00:00"

"translator": {

"type": "TabularTranslator",

"columnMappings": "SalesAmount:SalesAmount,OrderTimestamp:OrderTimestamp"

"parallelCopies": 10

"inputs": [

{

"name": "Orders"

}

"outputs": [

{

"name": "Orders2"

}

"policy": {

"timeout": "1.00:00:00",

"concurrency": 1,

"executionPriorityOrder": "NewestFirst",

"style": "StartOfInterval",

"retry": 3,

"longRetry": 0,

"longRetryInterval": "00:00:00"

"scheduler": {

"frequency": "Hour",

"interval": 1

"name": "Activity-0-[dbo]_[Orders]->[dbo]_[Orders2]"

}

"start": "2017-03-21T10:45:38.999Z",

"end": "2099-12-30T23:00:00Z",

"isPaused": false,

"hubName": "my_hub",

"pipelineMode": "Scheduled"

}

Note that we specify a “sqlReaderQuery” this time which selects the right subset of data for the slice. We use WindowStart and WindowEnd this time instead of SliceStart and SliceEnd earlier. At this point is does not matter as ADF requires both to be the same. WindowStart and WindowEnd refer to the pipeline start and end times, while SliceStart and SliceEnd refer to the slice start and end times. Using the “translator” properties we specify which columns to map – note that we copy over SalesAmount and OrderTimestamp exclusively.

There you have it – a fully incremental, repeatable data pipeline in Azure Data Factory, thanks to setting up a smart source query and using the “sliceIdentifierColumnName” property. The full source code is available on Github. More info on how this works is available in the official documentation.

Questions? Remarks? Let me know!

Dutch elections analyzed in Power BI

on March 14, 2017

by Jeroen ter Heerdt 0 Comments

Just in time for the elections in the Netherlands: I made an analysis using Power BI of the results of the elections since 1918. It is published (in Dutch) on the official Microsoft the Netherlands blog: https://blogs.microsoft.nl/technologieenmaatschappij/tweede-kamerverkiezingen-technologie-brengt-uitslagen-tot-leven/.

Enjoy!

Category Archives

Updated: Annual radio countdown Top 2000 in Power BI

Like this:

Farewell Tour

Like this:

Power BI Connector for CBS Open Data / Statline updated

Like this:

Ultimate Time Based Calculations Cheat Sheet for DAX / Power BI (including Week based calculations)

Like this:

Mapping Dutch geographical data using Power BI

Like this:

Updated: Annual radio countdown Top 2000 in Power BI

Like this:

SQL Server 2017: It’s here!

Like this:

Power BI Connector for CBS Open Data / Statline

Gebruik

Installatie

English

Like this:

Implementing incremental data load using Azure Data Factory

The linked services

The datasets

The first pipeline (from Azure Table to SQL)

The second pipeline (from SQL to SQL)

Like this:

Dutch elections analyzed in Power BI

Like this:

Category Archives

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Gebruik

Installatie

English

Share this:

Like this:

The linked services

The datasets

The first pipeline (from Azure Table to SQL)

The second pipeline (from SQL to SQL)

Share this:

Like this:

Share this:

Like this: