Search code examples
powerbipowerquerym

Dynamically get count of customers by year

I have data like below enter image description here

and would like to show it in a Power BI visual like this.

enter image description here

I am aware of creating pivoting and unpivoting the data, But I am looking of the count of customers in few buckets (lets say gap of 5 years) and it should be dynamic (I know this can be achieved using M language, but I am not able to wrap my head around it) Please could someone provide insights about this.

Solution

  • Create a calculated colum:

    5 Year Bin =
FLOOR ( DIVIDE ( data[Year], 5 ), 1 ) * 5 & "-"
    & FLOOR ( DIVIDE ( data[Year], 5 ), 1 ) * 5 + 4

    This looks like:

    Year 5 Year Bin
    1910 1910-1914
    1911 1910-1914
    1912 1910-1914
    1913 1910-1914
    1915 1915-1919
    1916 1915-1919
    1917 1915-1919
    1918 1915-1919
    1919 1915-1919
    1920 1920-1924
    1921 1920-1924
    1922 1920-1924
    1923 1920-1924
    1924 1920-1924
    1925 1925-1929
    1926 1925-1929
    1927 1925-1929
    1928 1925-1929
    1929 1925-1929
    1930 1930-1934
    1931 1930-1934

    To get the "5 Years Bin" to the columns add a matrix visualisation and configure it to show "5 Years Bin" in the columns and CountOfCustomers as values:

    Create matrix visualisation

    The result looks like this:

    1910-1914 1915-1919 1920-1924 1925-1929 1930-1934 Total
    8 10 16 41 23 98