Forum Discussion

MathiasBI's avatar
MathiasBI
Regular Visitor
3 years ago
Solved

Creating calculated columns which show value from four different intervals

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 +++ ...
  • MathiasBI's avatar
    MathiasBI
    3 years ago

    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]))

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
MathiasBI
Regular Visitor
3 years ago

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]))