Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors