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 +++

 

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..

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

2 Replies

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