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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
talex
Regular Visitor

Calculating and counting moths in filter

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

 

3 ACCEPTED SOLUTIONS
Whitewater100
Solution Sage
Solution Sage

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..
Whitewater100_0-1657328544548.png

 

 
Thanks!
 

 

View solution in original post

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.
 
Whitewater100_1-1657916978381.png

 


 

 
 

View solution in original post

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

View solution in original post

9 REPLIES 9
talex
Regular Visitor

Hi, 

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

Thank you!!!

talex
Regular Visitor

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. 

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.
 
Whitewater100_1-1657916978381.png

 


 

 
 

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. 

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

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

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

Whitewater100
Solution Sage
Solution Sage

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..
Whitewater100_0-1657328544548.png

 

 
Thanks!
 

 

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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