Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
salabb
Regular Visitor

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

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
 
 
 
 
2 REPLIES 2
salabb
Regular Visitor

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:

salabb_0-1692979960687.png

 

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
Super User
Super User

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors