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

Be 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

Reply
Datachris3000
Frequent Visitor

Sum of each year and extrapolation of actual year

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

 
MeldungCategoryDate
1Ausleger01.10.2018
2BD05.04.2019
3BE20.01.2020
4BE15.06.2018
5Ausleger11.04.2019
6BD29.03.2018
7BE15.05.2019
8BD

21.06.2019

9BE

01.01.2018

10BD

21.05.2019

11BE

11.11.2018

12BD

21.12.2018

 

The result should look like that in a Matrix e.g.:

Category20182019*2020
Ausleger110
BE313
BD230
 

 

If anyone has an easy way to solve that issue, I would be very thankful!

 

Have nice day,

Chris

 

6 REPLIES 6
v-eachen-msft
Community Support
Community Support

Hi @Datachris3000 ,

 

Do you mean to show actual values and extrapolated values in the same matrix?

4-1.PNG

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

It is not quite the solution. The result is 0 for the required fields. 

Maybe I did sth wrong with adjusting the formula. 
Date and "Meldung" are 2 different Tables in my file. Here is how I adjusted your formula: 
HoRe 2020 =
var a = COUNT(Servicemeldungen[Meldung])
var b = CALCULATE(COUNT(Servicemeldungen[Meldung]);FILTER('Datum';Datum[Jahr]=CALCULATE(MAX(Datum[Jahr]);ALL(Datum))))
var c = ALCULATE(COUNT(Servicemeldungen[Meldung]);ALLEXCEPT(Servicemeldungen;Servicemeldungen[Objektteil]);FILTER(Datum;Datum[Jahr]<>CALCULATE(MAX(Datum[Jahr]);ALL(Servicemeldungen))))
return
IF(ISINSCOPE(Datum[Jahr]);
a;b/c*12)+0
 
Do you see any mistake?
tkirilov
Resolver I
Resolver I

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

labuser1235
Helper IV
Helper IV

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

 

snip2.PNG

Sorry, I had a typo there. 

The correct results for the extrapolation of 2020 would be

(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?

Helpful resources

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

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.