Creating calculated columns which show value from four different intervals

[MathiasBI]

Hi!

I'm trying to create four columns that shows four different intervals:

Today - 1 month to date

1 month to date - 2 months to date

2 months to date - 3 months to date

3 months to date +++

I'm using a column which shows 'Due Date' to determine which column it should belong, and another column 'Amount (LCY)' that needs to be calculated based on the date intervals.

I've tried different types of DAX code, but I keep getting a lot of value in no man's land..

[Anonymous]

Hi @MathiasBI ,

Please have a try.

column =
VAR _1 = table[date]
VAR _2 =
    DATEDIFF ( _1, TODAY (), DAY )
RETURN
    IF (
        _2 <= 30
            && _2 >= 0 = 0,
        "Today - 1 month to date",
        IF (
            _2 <= 60
                && _2 > 30,
            "1 month to date - 2 months to date",
            IF (
                _2 <= 90
                    && _2 > 60,
                "2 months to date - 3 months to date",
                "3 months to date +++"
            )
        )
    )

column =
CALCULATE (
    SUM ( table[value] ),
    FILTER ( table, table[column] = EARLIER ( table[column] ) )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MathiasBI · Accepted solution]

Hi @Anonymous ,

I ended up creating variables for the past 1, 2 and 3 months, and equally for 1 month minus 1 day, 2 months minus 1 day etc.. like this:
Var 1m-1d = Date(Year(TODAY()), Month(TODAY()) - 1, Day(TODAY()-1))

Then I used this formula to calculate the total amount that was due between two dates:

Due 1m-2m = CALCULATE(SUM([Amount]),DATESBETWEEN([Due Date], [2m before today], [1m-1d after today]))