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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Svante109
Frequent Visitor

SUMX Calculating wrongly

I am working on a report the is supposed to show invoices and how they impact past, current and future periods. This is done on a calendar month to month basis. All calculations are done by measures.

 

The Past Period is as follows

 

 

 

Past Period Amount = SUMX(nav_subscription_entry,[AMOUNT_DKK]/[Duration Month] * [Past Periods Months])

 

 

 

This consists of three measures.

 

Amount_DKK

 

 

 

AMOUNT_DKK = ROUND(SUMX(nav_subscription_entry,nav_subscription_entry[Amount(DKK)]),0)

 

 

 

Duration Month

 

 

 

Duration Month = 
VAR from_date   = IF(MIN(nav_subscription_entry[from-date])=DATE(1753,01,01),DATE(2001,01,01),MIN(nav_subscription_entry[from-date]))
VAR to_date     = MAX(nav_subscription_entry[to-date])
return
(DATEDIFF(from_date,to_date,DAY)+1)/365*12

 

 

 

and Past Periods Months

 

 

 

Past Periods Months = 
Var from_date = MINX(nav_subscription_entry,nav_subscription_entry[from-date])
var as_of_date = EOMONTH(MAXX('Calendar','Calendar'[EoM]),-1)
var dato_difference = DATEDIFF(from_date,as_of_date,DAY)+1
return
IF(IF(dato_difference/365*12<0,0,dato_difference/365*12)>[Duration Month],[Duration Month],IF(dato_difference/365*12<0,0,dato_difference/365*12))

 

 

 

 

The Amount_DKK measure is not the same as the amount in the datasample provided below. The amount column is the actual amount the is on the invoice. As you can see on the first and second line, the Past Period Amount doesn't equal the total amount, even though the from and to date are all in past periods.

document noposting datefrom dateto datePast Period AmountCurrent Period AmountFuture Period AmountAmount
96125612-01-202101-01-202031-12-2020 $                      236.826,00 $                                               -   $                                            -   $         237.034,00
84802204-01-202101-07-202031-12-2020 $                        31.900,00 $                                               -   $                                            -   $           31.912,00
44201974208-01-202101-04-202031-03-2023 $                        13.471,00 $                                  1.519,00 $                             38.650,00 $           53.627,00
51744707-01-202101-04-202031-03-2023 $                        10.791,00 $                                  1.216,00 $                             30.959,00 $           42.974,00
44201925705-01-202101-09-202031-08-2023 $                          7.086,00 $                                  1.800,00 $                             54.711,00 $           63.610,00

 

If I don't include the SUMX part of the measure, then the total is wrong, but somehow PowerBI makes a miscalculation on the periods amounts when using the SUMX function.

1 ACCEPTED SOLUTION
Svante109
Frequent Visitor

I managed to find the solution myself. The issue was with rounding the numbers throughout the report, and not the formulas. 

View solution in original post

8 REPLIES 8
Svante109
Frequent Visitor

I managed to find the solution myself. The issue was with rounding the numbers throughout the report, and not the formulas. 

Geradav
Responsive Resident
Responsive Resident

@Svante109  I think you need to help us help you a little. Give us an example of the output you would expect.
What do you consider past period, current period, and future period?
Is the current period the current month in which we are today? So, today's current period is January?

The past period, is anytime that is before the start of the current month

The Current period is the current month

The Future period is any time after the end of the current month.

 

In the top line, I would expect an outcome of past period of 237.034,00 (as that is the total amount), but am getting an output of 236.826,00. 

Geradav
Responsive Resident
Responsive Resident

ok thanks for the clarification

Geradav
Responsive Resident
Responsive Resident

@Svante109 

 

You can be sure that Power BI doesn't make any miscalculation 😋

Most probably you made a mistake somewhere creating your measure. Not easy.

 

Could you share with us an anonymous sample dataset? That will make it way much easier for us to help.

 

David

I have tried to anonymize it in this pbix

 

https://www.dropbox.com/s/mzggq2fz9jo9i2s/Subscription%20entry%20normalisation.pbix?dl=0

 

Thank you David

Geradav
Responsive Resident
Responsive Resident

Kindly provide some information about which measure exactly is incorrect and what should be the expected result.

I'm a bit confused.

Its the 

 

"Past Period Amount"

"Current Period Amount"

"Future Period Amount"

 

That are all wrong

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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