cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 New Contributor
New Contributor

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 New Contributor
New Contributor

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

Highlighted
Elice Frequent Visitor
Frequent Visitor

Re: Consolidate data for a time range

Thanks a lot! 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 150 members 1,626 guests
Please welcome our newest community members: