topic Re: DAX formula for latest month based on selected filters in DAX Commands and Tips

DAX formula for latest month based on selected filters

afbraga66 — Thu, 15 Jul 2021 10:26:07 GMT

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é

Re: DAX formula for latest month based on selected filters

Anonymous — Thu, 15 Jul 2021 11:06:35 GMT

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

Re: DAX formula for latest month based on selected filters

afbraga66 — Thu, 15 Jul 2021 14:14:47 GMT

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é

Re: DAX formula for latest month based on selected filters

Anonymous — Thu, 15 Jul 2021 14:23:58 GMT

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.

Re: DAX formula for latest month based on selected filters

afbraga66 — Thu, 15 Jul 2021 14:38:14 GMT

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é

Re: DAX formula for latest month based on selected filters

Anonymous — Thu, 15 Jul 2021 15:27:41 GMT

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.

Re: DAX formula for latest month based on selected filters

afbraga66 — Thu, 15 Jul 2021 20:42:52 GMT

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?

Thank you.

Best regards,

André

Re: DAX formula for latest month based on selected filters

Anonymous — Fri, 16 Jul 2021 09:40:16 GMT

//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 )

Re: DAX formula for latest month based on selected filters

afbraga66 — Fri, 16 Jul 2021 11:15:10 GMT

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?

Thank you.

Best regards,

André

Re: DAX formula for latest month based on selected filters

Anonymous — Fri, 16 Jul 2021 11:38:19 GMT

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.

Re: DAX formula for latest month based on selected filters

Anonymous — Fri, 16 Jul 2021 11:50:34 GMT

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

Re: DAX formula for latest month based on selected filters

afbraga66 — Fri, 16 Jul 2021 12:08:40 GMT

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é

Re: DAX formula for latest month based on selected filters

Anonymous — Fri, 16 Jul 2021 12:22:37 GMT

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.

Re: DAX formula for latest month based on selected filters

afbraga66 — Fri, 16 Jul 2021 13:53:41 GMT

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é

Re: DAX formula for latest month based on selected filters

Anonymous — Fri, 16 Jul 2021 14:01:19 GMT

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.

Re: DAX formula for latest month based on selected filters

afbraga66 — Fri, 16 Jul 2021 14:17:17 GMT

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.

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

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é

Re: DAX formula for latest month based on selected filters

Anonymous — Fri, 16 Jul 2021 14:30:39 GMT

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.

Re: DAX formula for latest month based on selected filters

afbraga66 — Fri, 16 Jul 2021 14:41:23 GMT

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.