calculate total with dynamic dates between previous 2nd and 5th month

[Rubal_Islam]

Hi There,

I need help with 3 dynamic formulas for the below table. I do have a date table which is connected with the Delivery Date.

1: I need total order amount between previous 2nd and Previous 5th Month (i dont want to hard key the dates)
2: I need total order amount where date is higher/older than 6 months.

3: I need total order amount where date is null or left blank.

[Anonymous · Accepted solution]

HI @Rubal_Islam,

You can add a calculated column to check the current date and system date to remark flag then use this flag as a condition to coding formula to aggeration record amounts.

Calculate column flag:

Flag =
VAR diff =
    DATEDIFF ( 'Table'[Date], TODAY (), MONTH )
RETURN
    IF (
        'Table'[Date] <> BLANK (),
        SWITCH (
            TRUE (),
            diff >= 6, "Over 6 Months",
            diff < 6
                && diff >= 2,
                "Previous 2nd ~ 5th Months",
            diff = 1, "Previous Months",
            diff = 0, "Current Month",
            "Furture Month"
        ),
        "Null"
    )

Measure formula:

formual =
CALCULATE (
    SUM ( Table[Amount] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Flag] )
)

Regards,

Xiaoxin Sheng

[Rubal_Islam]

I have sorted out the Null Date as per below

Null date = CALCULATE(sum(Data_Source[Order Line Amount]),

FILTER(Data_Source,Data_Source[Required Delivery Date]=BLANK()))