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 no | posting date | from date | to date | Past Period Amount | Current Period Amount | Future Period Amount | Amount |
| 961256 | 12-01-2021 | 01-01-2020 | 31-12-2020 | $ 236.826,00 | $ - | $ - | $ 237.034,00 |
| 848022 | 04-01-2021 | 01-07-2020 | 31-12-2020 | $ 31.900,00 | $ - | $ - | $ 31.912,00 |
| 442019742 | 08-01-2021 | 01-04-2020 | 31-03-2023 | $ 13.471,00 | $ 1.519,00 | $ 38.650,00 | $ 53.627,00 |
| 517447 | 07-01-2021 | 01-04-2020 | 31-03-2023 | $ 10.791,00 | $ 1.216,00 | $ 30.959,00 | $ 42.974,00 |
| 442019257 | 05-01-2021 | 01-09-2020 | 31-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.
