Create a Calculated Column from Different Tables into a measure

Hi Anonymous

Based on my understanding, your requirement is to:

count Class A and B in these three sheets for each department, finally show the count monthly.

Right?

I make a test as below:

In Queries Editor, create a “new Query”,

New source->blank query

Then open Advanced editor, write code

let

    Source1 =Table.SelectColumns(Sheet1,{"date","from","class"}),

    Source2 =Table.SelectColumns(Sheet2,{"date","from","class"}),

    Source3 =Table.SelectColumns(Sheet3,{"date","from","class"}),

    #"Appended Query" = Table.Combine({Source1, Source2, Source3})

in

#"Appended Query"

Then split column

Then close &&apply,

Create a calendar date table, using formula after clicking on “New Tbale”,

calendar date = CALENDARAUTO()

create relationship between “calendar table” and “new query”

create a measure in your table “new Query”,

Measure =
CALCULATE (
 COUNT ( 'new Query'[class] ),
 FILTER (
 'new Query',
 'new Query'[from.1] = MAX ( 'new Query'[from.1] )
 && ( 'new Query'[class] = "A"
 || 'new Query'[class] = "B" )
 )
)

then add “date” in “calendar table” in the Axis, add measure in “Value”, add “from.1” in the “Legend”.

Best Regards

Maggie