Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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...
Solved! Go to 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:
Hi:
This is the measure that is tied to your date table. (Mine is named calendar.)
That is great! If you feel like it is a solution can you mark it so? Thank you.
Hi,
it works so far, just needs some little adjustments to be perfect. I am working on it.
Thank you!!!
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.)
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.
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
44 | |
35 | |
25 | |
22 | |
22 |