cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Elice Frequent Visitor
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

Accepted Solutions
sturlaws Senior Member
Senior Member

Re: Consolidate data for a time range

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 Senior Member
Senior Member

Re: Consolidate data for a time range

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

Elice Frequent Visitor
Frequent Visitor

Re: Consolidate data for a time range

Thanks a lot! 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 1,046 guests
Please welcome our newest community members: