March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Community,
I'm new using Power BI and I do have a problem that might be quite easy to solve for experienced members.
I would like to create a measure, which has the number of 'Meldungen' for each year AND extrapolated values for the actual year (YTD). The extrapolation should be by month (e.g. last month of the table is April 2020 = (sum of 2020 divided by 4 ) * 12
The fictional raw data table looks similar like that and includes values from 01.01.2018 until 30.04.2020
Meldung | Category | Date |
1 | Ausleger | 01.10.2018 |
2 | BD | 05.04.2019 |
3 | BE | 20.01.2020 |
4 | BE | 15.06.2018 |
5 | Ausleger | 11.04.2019 |
6 | BD | 29.03.2018 |
7 | BE | 15.05.2019 |
8 | BD | 21.06.2019 |
9 | BE | 01.01.2018 |
10 | BD | 21.05.2019 |
11 | BE | 11.11.2018 |
12 | BD | 21.12.2018 |
The result should look like that in a Matrix e.g.:
Category | 2018 | 2019 | *2020 |
Ausleger | 1 | 1 | 0 |
BE | 3 | 1 | 3 |
BD | 2 | 3 | 0 |
If anyone has an easy way to solve that issue, I would be very thankful!
Have nice day,
Chris
Hi @Datachris3000 ,
Do you mean to show actual values and extrapolated values in the same matrix?
I created a measure and used total column to show extrapolated values.
You could also show it in 2020 column with conditions.
Here is my test file for your reference.
It is not quite the solution. The result is 0 for the required fields.
Hi @Datachris3000 ,
I don't think you need a measure, as you can just use "Meldung" as the value and use the dropdown to change it to Count, and then use Category for rows and Year for Columns in a Matrix visual. I must admit I'm a bit confused by the dividing by 4 part, but the above should work anyway. Hope this helps.
Best,
Tom
@Datachris3000 could you be more specific on your result and the measure you would like to create ?
I got the below output, unsure what you meant by 13 in 2020.
Sorry, I had a typo there.
The correct results for the extrapolation of 2020 would be 3
(1x Meldung in January 2020 - last month is April 2020 (=4); Sum of Meldung from Jan to Apr. = 1
Extrapolation results = 1 / 4 * 12 = 3)
Is that understandable?
Oh I see, that does make sense, but I still don't think you need a measure for that as having Sum of Meldung as Value, Category as Rows and Year (only) as Columns in a matrix, should give you the results you seek. Or am I missing something completely?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
82 | |
69 | |
53 | |
44 |
User | Count |
---|---|
202 | |
106 | |
100 | |
64 | |
56 |