Forum Discussion

Svante109's avatar
Svante109
Frequent Visitor
5 years ago
Solved

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.

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

8 Replies

  • Svante109's avatar
    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's avatar
    Geradav
    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

  • Geradav's avatar
    Geradav
    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?

    • Svante109's avatar
      Svante109
      Frequent Visitor

      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's avatar
        Geradav
        Responsive Resident

        ok thanks for the clarification