AlenKovacevic
New Member
- Joined
- Apr 1, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi all,
I am trying to get the month to date calculation correct but I am struggling with the last part of it. In order to have prior months, there is no issue but when trying to compare MTD of this month (15/11/2021) with the one of previous year (15/11/2020), the MTD calculation of previous year shows the total value of the month, while I would like to calculate the value until the 15/11/2020.
To get the correct MTD comparison day by day, I am using the following (which basically says that if the latest date in my transaction table is smaller than the last date of the max date in the date table, the amount of sales is set to 0: both applying for current year and last year)
Sales LY CM =
IF(
LASTDATE(Dates[Date]) > LASTDATE(PBI_SalesQuery[Date]), 0 ,
CALCULATE(
[Sales CM (MTD)],
SAMEPERIODLASTYEAR(Dates[Date])
)
)
But when I group the data in a monthly view, the subtotal for November for the current month of last year (Nov-20) is set to 0 and does not sum what is shown as a day to day in Nov-20 ... And I am not sure to understand why? While I would like to have that sum appear, in that way I can have the MTD of Nov-21 compared to the MTD of Nov-20.
Many thanks for your help!
I am trying to get the month to date calculation correct but I am struggling with the last part of it. In order to have prior months, there is no issue but when trying to compare MTD of this month (15/11/2021) with the one of previous year (15/11/2020), the MTD calculation of previous year shows the total value of the month, while I would like to calculate the value until the 15/11/2020.
To get the correct MTD comparison day by day, I am using the following (which basically says that if the latest date in my transaction table is smaller than the last date of the max date in the date table, the amount of sales is set to 0: both applying for current year and last year)
Sales LY CM =
IF(
LASTDATE(Dates[Date]) > LASTDATE(PBI_SalesQuery[Date]), 0 ,
CALCULATE(
[Sales CM (MTD)],
SAMEPERIODLASTYEAR(Dates[Date])
)
)
But when I group the data in a monthly view, the subtotal for November for the current month of last year (Nov-20) is set to 0 and does not sum what is shown as a day to day in Nov-20 ... And I am not sure to understand why? While I would like to have that sum appear, in that way I can have the MTD of Nov-21 compared to the MTD of Nov-20.
Many thanks for your help!
