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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
afbraga66
Helper III
Helper III

DAX formula for latest month based on selected filters

Hi,
 
I am trying to get the duration minutes for the lastest month of the selected months in a slicer.
In case none is selected then it should consider the latest month/max.
Currently I have the formula below, but it is clearly not working.
Also DOWNTIME[MONTH] is a numeric field (1,2,3,...,12), so I would probably have to convert D_Calendar[Month] before evaluating.
 
mDurationLatestSelectedMonths(min) =
CALCULATE(sum(DOWNTIME[Duration Minutes]),KEEPFILTERS(DOWNTIME[MONTH]=VALUES(D_Calendar[Month])))
 
Thank you.
Best regards,
André
17 REPLIES 17
Anonymous
Not applicable

 

 

mDurationLatestSelectedMonths(min) =
// MonthID must be a unique number
// that identifes months across
// years. Best to number them from
// 1 up to the number of all months in
// the calendar. It is very important
// to have such a column in you date dimension.
// Your fact table should not have
// a month in it but a suitable Date,
// a key from your calendar. Calendars
// should ALWAYS be on the date granularity
// even if you only need months. ALWAYS.
var SelectedMonthOrMaxMonth = MAX( D_Calendar[MonthID] )
var NewTimeFilter =
	CALCULATETABLE(
		DISTINCT( D_Calendar[Date] ),
		D_Calendar[MonthID] = SelectedMonthOrMaxMonth,
		REMOVEFILTERS( D_Calendar )
	)
var Result =
	CALCULATE(
	    SUM( DOWNTIME[Duration Minutes] ),
	    NewTimeFilter
	)
return
	Result

 

 

Hello,

Thanks for your response.

I understand what you said about not having the month in the fact table.

But then how do I do when the Calendar has more months than there is data in my fact table?

For example, the Calendar goes up to August, but I have only data until July.

With the way you have done it, it will return nothing, because it will be getting data for August and there is none.

That's why I had it there and was trying to use it. Doesn't mean it is right, just explaining the reason why.

Any idea how can I overcome that.

Thank you.

 

Best regards,

André

Anonymous
Not applicable

I understand what you said about not having the month in the fact table.

But then how do I do when the Calendar has more months than there is data in my fact table?

 

> What you do depends on what you want to do. You have to define what you want.

 

For example, the Calendar goes up to August, but I have only data until July.

 

> Calendars MUST always contain full years. They can't just go up to some date. They have to go to the last day of a year.

 

With the way you have done it, it will return nothing, because it will be getting data for August and there is none.

 

> Is this a problem? Since there's no data, no data is returned. Makes sense.

 

That's why I had it there and was trying to use it. Doesn't mean it is right, just explaining the reason why.

Any idea how can I overcome that.

 

You can always filter your slicer(s) via Fiter Pane to only show what you want.

 

Hello,

 

> What you do depends on what you want to do. You have to define what you want.

Yes, I agree.

 

> Calendars MUST always contain full years. They can't just go up to some date. They have to go to the last day of a year.

Indeed. I was just using August as an example.

 

> Is this a problem? Since there's no data, no data is returned. Makes sense.

Well it is, because once I put the data on a table it will only show data for months that have data. And then I wan to use another metric to sort descending by the latest month in which there is data.

 

Thank you.

 

Best regards,

André

Anonymous
Not applicable

@afbraga66 

 

As I mentioned, you could always filter the whole visual, page or even the whole report to only show your dates up to the last one that has data. This is not a problem.

Hello,

 

I tried to do the expression you carefully made.

This is the result of the expression. The total is correct, but I expected it to give only values for July. How can I achieve that maintaining the same idea?

afbraga66_0-1626381644583.png

Thank you.

 

Best regards,

André

Anonymous
Not applicable

 

 

//I am trying to get the duration
// minutes for the latest month
// of the selected months in a slicer.
// In case none is selected, then it
// should consider the latest month/max.

[Total Downtime] = SUM( Downtime[Duration Minutes] )

// Please be aware that this measure is
// sensitive to all selections in all
// dimensions.
[Your Measure] =
SWITCH( true(),

	ISCROSSFILTERED( 'D_Calendar' ),
		// If the calendar is filtered/crossfiltered,
		// then we grab the max month visible across
		// the whole selection of dates (not only
		// the current one).
		var LastVisibleMonth =
			CALCULATE(
				MAXX( 'D_Calendar'[MonthId] ),
				ALLSELECTED( 'D_Calendar' )
			)
		// Then we check if the currently visible
		// period of time is fully contained within
		// the last visible month.
		var IsCurrentPeriodContainedInLastVisibleMonth =
			SELECTEDVALUE( 'D_Calendar'[MonthId] )
				= LastVisibleMonth
		var Result = 
			// ... and if it is, we calculate the
			// corresponding downtime, otherwise we
			// return blank.
			if( IsCurrentPeriodContainedInLastVisibleMonth,
				[Total Downtime]
			)
		return
			Result,
		
	// If the Calendar is not filtered/crossfiltered,
	// we just grab the very last month for which
	// there is data in Downtime, and return the
	// total downtime in this very month.
	var VeryLastMonthWithDowntime = 
		CALCULATE(
			MAXX(
				SUMMARIZE(
					Downtime,
					D_Calendar[MonthID]
				),
				D_Calendar[MonthID]
			),
			REMOVEFILTERS( )
		)
	var Result =
		CALCULATE(
			[Total Downtime],
			'D_Calendar'[MonthID] = VeryLastMonthWithDowntime
		)
	return
		Result
)

 

 

Hello,

 

Thank you so much for helping me, you are awesome.

This seems to be working, the only issue I am having now is that when I try to sort by descending it is not actually sorting. Any idea why the numbers are not sorted and the nulls don't come in last?

afbraga66_1-1626434023230.png

 

Thank you.

Best regards,

André

Anonymous
Not applicable

The visual does not sort because it does not know which column you're talking about since there are many columns with the same name. So, do I have to sort by the "last month" column in 5, 6 or 7? Or maybe under Total? Since its a column in matrix you can try to click the header over the correct column.... it may then sort it.

Anonymous
Not applicable

It looks like the matrix can only sort by the Total, not by individual columns.

Hello,

 

Because matrix only sorts by total, that's why I was trying to develop this formula for the latest month (5-May, 6-June, 7-July). Then I would had the formula to the values, hide it with word wrap off and sort descending by it. When I first tried I used this code to test it.

mDurationLatestMonth(min) = CALCULATE(sum(DOWNTIME[Duration Minutes]),KEEPFILTERS(DOWNTIME[MES_FABRIL]=MONTH(TODAY())))

It works, but is fixed to today and sometimes there is no data yet for today, hence me opening this question for help in trying to get the max month dynamically.

 

Thank you.

 

Best regards,

André

Anonymous
Not applicable

Why are there no numbers in the Total column for the "last month" measure? Have you applied a filter to the visual/page/report to only go with dates in the date dimension as far as the last month for which there is data in Downtime? You have to have a column in your date dimension that says True for dates before or in the last month present in Downtime and False otherwise. Then you'll filter by this column in the Filter Pane and you'll get what you're after.

Hello,

 

Ok, so I took the time to build a mock up file to be easier I think. It only has one table and with no Calendar dimension on purpose. I know it is not how data models are built, but just for this example I want to do it only with that table. The file is in here https://we.tl/t-XlZUWPgAKL (couldn't find how to upload it in the reply).

So I want to sort by month number 5, 6  or 7 depeding on which is the latest according to my filters or absence of filtering, and for that I need to create a formula that gives me just that. So that when I add it to the matrix I will have two formulas. One that gives values for all the months, and one that just gives for the latest. Then I sort by that one, and hide it from the matrix.

 

Thank you,

Best regards,

André

Anonymous
Not applicable

Wait! So you're saying that you want to sort the columns, not rows! That's a whole different story. I have never seen this would be possible anywhere and I don't think it would be possible here whatever you do. It's just not gonna happen. Matrices don't have this ability however hard you try. I'd like to be proven wrong. Really.

Hi,

In the example I gave I have this in the image below. It is being sorted by the total value, that's what power bi does out of the box. I don't want that.

 

afbraga66_0-1626444613017.png

I want it to look like this. Of course I would hide the second formula then.

afbraga66_1-1626444874171.png

I did that with this second formula:

 

Today Month = CALCULATE([mDuration(min)], KEEPFILTERS(DOWNTIME[MES_FABRIL]=MONTH(TODAY())))

 

But this is fixed to todays month, and I can't have it like that. I want it to have the dynamic filtering that I explained before.

 

Thank you.

 

Best regards,

André

Anonymous
Not applicable

OK, I don't understand. You said you wanted to sort the columns, not rows, meaning you wanted to dynamically change the order of the columns based on some value. Now it seems you want to sort rows and leave the columns intact. Sorry, I don't follow. I told you exactly what to do to get the total to be my measure and then sort rows by it. Can't do much more. Sorry.

What I always wanted is in my latest post and in the file I provided if you could please look into it? I apoligize if I made it confusing for you. I am pretty new to this and I have difficulty in DAX, hence asking for help. Sorry.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors