topic Re: Calculating and counting moths in filter in DAX Commands and Tips

Calculating and counting moths in filter

talex — Fri, 08 Jul 2022 22:32:48 GMT

Hi Guys,

I am using a global filter where I can choose the date range to show only key figures within that range on my report. Lets say I select here 01.01.2022-30.06.2022. For one particular key figure I would like to divide it by 6 in this case as we are selecting 6 months and when selecting 01.01.2022-31.12.2022 therefore divide by 12.

How can I count the months from the page filter and use this in my calculation easily ? I am beginner, so should no be very complicated if possible...

Re: Calculating and counting moths in filter

Whitewater100 — Sat, 09 Jul 2022 01:02:51 GMT

Hi:

You could try this approach which may be helpful if you want to see fractions of months, if you starte on 1-15 and ended on 3-23 for example. 365/12 = 30.4

*I am assuming your date table is named"Dates" and is marked as a date table.

Here are the measures:

Start = MIN(Dates[Date])

End = MAX(Dates[Date])

Months in Period =

var _NoOfDays = INT( [End] - [Start])

return

DIVIDE(_NoOfDays,30.42,0)

OR just one measure:

Months in Period 1 Measure =

var _start = MIN(Dates[Date])

var _end = MAX(Dates[Date])

var _NoOfDays = INT( [End] - [Start])

return

DIVIDE(_NoOfDays,30.42,0)

This can be adjusted if needed. The format for the final measure is decimal with one decimal palce.

Image below..

Thanks!

Re: Calculating and counting moths in filter

talex — Mon, 11 Jul 2022 16:38:50 GMT

Hi,

it works so far, just needs some little adjustments to be perfect. I am working on it.

Thank you!!!

Re: Calculating and counting moths in filter

talex — Fri, 15 Jul 2022 11:50:57 GMT

One more thing gets on my nerves. When I select the dates 01.01.2022 - 31.12.2022 for example, then I don't get those dates as they do not exist in my table. Maybe I have only 25.01.2022 in January and 27.12.2022 in December, therefore I can only selct them and my calculation will not be 12 months, instead something around 11,... I could round now, but this will not work perfectly.

So how can I select 01.01.2022 - 31.12.2022 for instance, even when those values do not really exist (yet).

I just want to see the values beetwen those dates, but still want to be able to select any date I want in the filter.

Re: Calculating and counting moths in filter

talex — Fri, 15 Jul 2022 11:51:28 GMT

So how can I select 01.01.2022 - 31.12.2022 for instance, even when those values do not really exist (yet).

I just want to see the values beetwen those dates, but still want to be able to select any date I want in the filter.

Re: Calculating and counting moths in filter

Whitewater100 — Fri, 15 Jul 2022 20:29:59 GMT

Hi:

This is the measure that is tied to your date table. (Mine is named calendar.)

Months in Period 1 =

var _start = MIN(Calendar[Date])

var _end = MAX(Calendar[Date])

var _NoOfDays = INT(_end-_start)

return

DIVIDE(_NoOfDays,30.41,0)

It calculates no. of months between any two dates selected. Is this what you are looking for? Set format to Decimal , 1 place.

Re: Calculating and counting moths in filter

talex — Sat, 16 Jul 2022 14:33:49 GMT

Yes I got that, its almost what I want. I have a booking table and use the field departure date.

The thing is, there are no departures every single day. Therefore I might not have 01.01.2022 or 15.02.2022 or 31.12.2022 in the table. But I still want to be able to select from the beginning of a month to any date I want and then get the results within this range. I also want to calculate the number of months with my selection and not with the first and last date it finds within my selection.

I was also thinking about a seperate date table, but I don't really want to maintan a date table, do I ?

So isnt't there a function or a sytem table I can use, so that I do not depend on the entries I have in my table for the filter.

Re: Calculating and counting moths in filter

Whitewater100 — Sat, 16 Jul 2022 20:54:45 GMT

Hi:

Yes my assumption is you have separate date table which would have a realtionship to your fact table. The measure is being driven off the date table so you can aggregate anything you want between the dates you select, from the separate date table. This Date table should be continuous and be marked as Date table.

If you want to share an example of what you are trying to accomplish, I can check it out for you.

Thanks..

Re: Calculating and counting moths in filter

talex — Mon, 18 Jul 2022 14:25:43 GMT

Hi,

I think it works now. I have created a date table with DAX Function CALENDAR and connected it to the fact table and customized the code a little bit, just as you said.

Perfect. Thanks a lot!!!

Re: Calculating and counting moths in filter

Whitewater100 — Tue, 19 Jul 2022 00:55:02 GMT

That is great! If you feel like it is a solution can you mark it so? Thank you.