How to calculate in a calendar table field depending on another filtered table

[salabb]

Hello everyone,

I am trying to write some DAX to have a calculated field in a calendar table. This table is created by:

Calendar = CALENDAR(min(Table_name[date]),max(Table_name[date]))

Now I'd like to calculate a column within this Calendar table, let's call it "last_month", turning as "Yes" for all those days which are in the last month of the filtered Table_name and "No" for all the other ones. For example, if Table_name is filtered and the last date is 4th June 2023, then the new calculated field should say "Yes" for all days in June 2023, "No" for all the other ones.

At the moment I only manage to do it for the original unfiltered table as:

last_month = if(and(month(max([date]))=month([date]),year(max([date]))=year([date])),"Yes","No")

But of course it's not enough for my purposes. 😅

Thanks a lot for your help in advance. 😀

Best,

SA

[salabb]

Ok, thanks – I think I've just done what you recommended:

 First I created this measure to calculate last month across the dataset

last_month = MAXX(FILTER(ALLSELECTED(Table_name),Table_name[month]>1),Table_name[month])

Then this one to convert existing month column (integer between 1 and 12) into a measure:

month_ = CALCULATE(

    AVERAGE(Table_name[month]),

    ALLEXCEPT(Table_name,Table_name[month])

)

...and then this final measure, to flag months which are the last ones according to filters as "Yes" else as "No":

last_month_flag = IF(Table_name[month_] = Table_name[last_month],"Yes","No")

It works indeed:

My end goal is to use this last_month_flag as a grouping variable for aggregating sessions and have a sub-total for all the last_month_flag = "No" months to compare against the most recent month (last_flag_month = "Yes").

What is the best way to use this measure to aggregate other measures?

I have created a parameter out of this last_month_flag measure and written a measure for a KPI I need to summarize, but while I manage to summarize values for all the "No" months (monthly averages), the values for the last month are missing and so the labels "Yes" and "No" as the parameter/last_month_flag is being used as grouping parameter by row. I know, it's a lot. Below the KPI measure: 

KPI =
var tb =
SUMMARIZE (
CALCULATETABLE(
'Table_name'
),
'Table_name'[month],
"SUM",
CALCULATE(
SUM(Table_name[Sessions])
)
)

return

IF('Table_name'[last_month_flag]="No",CALCULATE(AVERAGEX(tb, CALCULATE(SUM(Table_name[Sessions]))),FILTER('Table_name','Table_name'[last_month_flag]="No")),CALCULATE(SUM(Table_name[Sessions]))
)

[johnt75]

You can't do it in a table, calculated tables and columns are only calculated during data load or refresh, so they don't pay any attention to filters or slicers. If you need something to react dynamically you need to use a measure.