Consolidate data for a time range

[Elice]

Hi,

I want to consolidate the amount of backlog from a time range, this formula should be dynamic. 

Backlog from 3 months and earlier (from current month) should be summarized and backlog from 4 months ahead and further (from current month) should be summarized. My purpose is to show a column chart that shows 1 bar with a consolidated backlog amount from 3 months and earlier, 1 bar for current month, 1 bar for oct, 1 bar for nov, 1 bar for dec, 1 bar for jan and 1 bar for the rest in the future.

I do not want to use the filter, because this will be a monthly manual task, which I want to prevent.

Date         Backlog Amount

04-2019   26

05-2019   25

06-2019   20

07-2019   15

08-2019   5

09-2019   10

10-2019   20

11-2019   50

12-2019   60 

01-2020   15 

02-2020   12

03-2020   25

[sturlaws · Accepted solution]

Hi,

you could make use of the Today-function, each time you refresh your model it will update the time ranges based on todays date.
As you want to have this on the axis, you have to have these ranges as columns, you cannot handle this as a measure only. Two ways of doing it, you can add a calculated column in you data table(or date table, if you have one). Or you can have stand-alone table with the timeranges, and have measure that places the values in the correct time range.

I have set up how you can do it in the data table. There is a couple of auxillary columns, which are not strictly necessary(all of them can be combined to one column in dax), but it makes it easier to see what happens.

pbix-file

cheers,
Sturla

[Elice]

Thanks a lot!