Power BI Exchange

Expand all | Collapse all

Problem with data aggregation at week level

  • 1.  Problem with data aggregation at week level

    Posted 12-05-2018 02:41 PM
      |   view attached

    I'm having an issue while aggregating data at week level.

    KPI: Calculate,<how many days on avg, does a client take to move from phase 1 to phase 2> capture this KPI and compare it WOW.

    first_seen_label_1 = CALCULATE(FIRSTDATE(tbl1[date_recorded]), tbl1[label]="label_1")

    first_seen_label_2 = CALCULATE(FIRSTDATE(tbl1[date_recorded]), tbl1[label]="label_2")

    avg_days = 
    VAR summary_table= SUMMARIZE(tbl1, tbl1[Client], "label_1_date", [first_seen_label_1], "label_2_date", [first_seen_label_2])
    AVERAGEX(summary_table, DATEDIFF([label_1_date],[label_2_date],DAY))

    For the data in "tbl1", for any week, if a client has labels changed in previous week, we should be able to pick up the first day when we saw those labels and use the dates to compute the average.


    **this KPI should only be calculated for list of clients recorded in that week (not all clients from the beginning).!

    I tried to ignore the date filter (week) by using ALL() function but it doesn't seem to fetch the right result.

    I have attached the sample pbix file below.

    Krish Kari
    Data Analyst


  • 2.  RE: Problem with data aggregation at week level

    Bronze Contributor
    Posted 12-05-2018 04:44 PM
    It looks like you basically want to filter the table you are doing the summarization on.

    Calculate your current week and then look for all clients that have rows corresponding to that week and use that as a filter on the table you pass into your `SUMMARIZE`.

    avg_days =
    VAR LatestWeek =
        MAX ( dates[week_no] )
    VAR CurrentClients =
        CALCULATETABLE ( VALUES ( tbl1[Client] ), dates[week_no] = LatestWeek )
    VAR summary_table =
        SUMMARIZE (
            FILTER ( tbl1, tbl1[Client] IN CurrentClients ),
            "label_1_date", [first_seen_label_1],
            "label_2_date", [first_seen_label_2]
        AVERAGEX ( summary_tableDATEDIFF ( [label_1_date], [label_2_date], DAY ) )

    Alexis Olson
    DFW, Texas

  • 3.  RE: Problem with data aggregation at week level

    Posted 12-06-2018 09:35 AM

    Thanks for the reply Alexis.

    I appreciate your response. The problem I'm facing is,

    When I use week number as a slicer and select one particular week, The first_dates measures are reflecting the FIRSTDATES in that week, but what I want is for it to ignore the week filter during the calculation of these measures but consider the  filters while calculating the average # days

    first_ver_lbl = CALCULATE(FIRSTDATE(tbl1[date]), tbl1[label]="label_1", ALLEXCEPT(tbl1,tbl1[client]))
    ​Here I tried to ignore the filter on tbl1[date] column and retain filter on tbl1[Client] column; in order to calculate the first date of occurrence of lable_1 for each client.
    The result is, even though I tested for a client that started on week 49, it's still showing the very first date in the table and populating the same date for all the clients.

    Any idea on how to  solve this.


    Krish Kari
    Data Analyst

  • 4.  RE: Problem with data aggregation at week level

    Bronze Contributor
    Posted 12-06-2018 11:31 AM
    Try using ALL(dates) instead of your ALLEXCEPT(tbl1, tbl1[Client]).

    Alexis Olson
    DFW, Texas