topic Re: Creating calculated columns which show value from four different intervals in DAX Commands and Tips

Creating calculated columns which show value from four different intervals

MathiasBI — Wed, 29 Mar 2023 13:06:05 GMT

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

Re: Creating calculated columns which show value from four different intervals

Anonymous — Fri, 31 Mar 2023 02:23:16 GMT

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.

Re: Creating calculated columns which show value from four different intervals

MathiasBI — Fri, 31 Mar 2023 11:57:47 GMT

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