Calculating and counting moths in filter

[talex]

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...

[Whitewater100 · Accepted solution]

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!

[Whitewater100 · Accepted solution]

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.

[talex]

Hi, 

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

Thank you!!!

[talex]

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. 

[Whitewater100 · Accepted solution]

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.

[talex]

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. 

[Whitewater100]

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..

[talex]

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!!!

[Whitewater100 · Accepted solution]

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

[Whitewater100 · Accepted solution]

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!

[talex]

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.