cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elice
Frequent Visitor

Consolidate data for a time range

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

1 ACCEPTED SOLUTION
sturlaws
Super User
Super User

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

View solution in original post

2 REPLIES 2
sturlaws
Super User
Super User

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

Thanks a lot! 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors