Get sum based on a different date to filter

[AliB]

Hi
I have a table that contains information at a given date (monthly). The table will contain duplicate lines except for the Report Run Date column.

I want to calculate the Total Amount where the Report Run Date = Rpt Previous Qtr End

I’ve tried the following but nothing gets returned. Where am I going wrong

I have a date table and the following
DataTable =
{
“Report_Run_Date”,
“InvNo”
“InvDate”
“Amount”
PrvQtrDate"
“CustomerNo”
“Group”

}

Groups =
{ “Group”}

I have the following measures:
Total Amount = sum(DataTable[Amount])

Rpt Previous Qtr End =
VAR CurrentDate = MAX(Dates[Date])
VAR MonthSelection = MONTH(CurrentDate)
VAR LastQuarterEnd =
SWITCH(
TRUE(),
MONTH(MonthSelection) <= 3,
Date(Year(MonthSelection)-1,12,31),
MONTH(MonthSelection_) <= 6,
Date(Year(MonthSelection_),3,31),
MONTH(MonthSelection_) <= 9,
Date(Year(MonthSelection_),6,30),
MONTH(MonthSelection_) <= 12,
Date(Year(MonthSelection_),9,30),
“Error”
)
VAR _PriorValue =
CALCULATE([AFDA Current GBP],ALL(Dates),Dates[Date]=_LastQuarterEnd)

RETURN

_PriorValue

[nikot.23]

Answer:

One way to tackle this is to get the current month sequence in the quarter and subtract that many months to get the last month of the previous quarter.

e.g. May is the 5th month and 2nd month in Q2, subtracting 2 from 5 will get us to March (last month of Q1). Jun is the 6th month and 3rd month in Q2, subtracting 3 from 6 will get us to March again.

You need an offset month column in your Calendar table, for the case that we span years; i.e. for Jan/Feb/Mar you would need the value for Dec of previous Year. Offsets make things easier in general.
offsetMonth = DATEDIFF( TODAY(), [Date], MONTH)

the measure for Total Amount is (you can modify for other measures):

Total Amount EndOfPrevQ =
VAR _currentMonth =
    MONTH ( MAX ( Dates[Date] ) ) // Get the month number of the current date
VAR _currentMonthOffset =
    MAX ( Dates[offsetMonth] ) // Get the offset month value for the current date
VAR _currentMonthSequenceInQ =
    MOD ( _currentMonth - 1, 3 ) + 1 // Calculate the sequence of the current month within the quarter
VAR _endPreviousQuarterOffset = _currentMonthOffset - _currentMonthSequenceInQ // Calculate the offset for the end of the previous quarter
VAR _valuePreviousQ =
    CALCULATE (
        [Total Amount],
        REMOVEFILTERS ( Dates ),
        Dates[offsetMonth] = _endPreviousQuarterOffset
    ) // Calculate the total amount for the end of the previous quarter
RETURN
    _valuePreviousQ

[AliB]

Thank you.