Search code examples
powerbidaxpowerquerypowerbi-desktopm

Average aggregation in difference of dates between two different tables

I have 2 tables:

  1. Calendar table
Date
-----
Dec 2021
Jan 2022
Feb 2022
Mar 2022
   .
   .
   .
  1. Event table
Event   | Last Date
-----------------
Event A | 01-Jan-2013
Event B | 01-Mar-2017
Event C | 01-Feb-2022
   .    |      .
   .    |      .
   .    |      .

I want to create a table that calculates the average difference in month between the calendar table and the last Date of event event table and aggregate them per month

Calculation is as follows:

For Dec 2021

Diff in month for Event A= 107
Diff in month for Event B= 57
Event C is not considered as the last Date> Dec 2021
Avg = (107+57)/2 = 82

For Jan 2022

Diff in month for Event A= 108
Diff in month for Event B= 58
Event C is not considered as the last Date> Jan 2022
Avg = (108+57)/2 = 83

For Feb 2022

Diff in month for Event A= 109
Diff in month for Event B= 59
Diff in month for Event C= 0
Avg = (109+58+0)/3 = 56

For Mar 2022

Diff in month for Event A= 110
Diff in month for Event B= 60
Diff in month for Event C= 1
Avg = (110+59+1)/3 = 57

Output table should be as follows:

Date     | Avg Diff
------------------------
Dec 2021 | 82
Jan 2022 | 83
Feb 2022 | 56
Mar 2022 | 57
   .     |
   .     |
   .     |

Any help implementing this through DAX(PowerBI) or PowerQuery is appreciated.

Solution

  • Result

    enter image description here

    Event table looks like this:

    enter image description here

    Calendar table looks like this:

    enter image description here

    Full code for calendar table to get result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcklNVjAyMDJUitWJVvJKzANxjMAct9QkBMc3sQjKiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Date", "Date2"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
let a =  (x)=> 
   let b = Table.SelectRows( Event, (y)=> y[Last Date] <= x[Date2] ), 
   c = Table.AddColumn(b, "diff", (z)=>( Date.Month(x[Date2]) +  (Date.Year(x[Date2])*12)) - (Date.Month(z[Last Date]) +  (Date.Year(z[Last Date])*12)) ),
   d = Table.RowCount(b),
   e = List.Sum(c[diff])
   in e/d
in a),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date2"})
in
    #"Removed Columns"