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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Variable Range based on date filter

Hello! 

I'm new user of PBI and I'm trying to create a graphic were shows me the range of KM per Item based on a Data Filter. 

The range I'm trying to get is:

1. >= 1.000 KM

2. Between 1001 to 2000 KM

3. Above 2001 KM

 

For exemple, if I select the Data Filter from 01/jan to 05/feb the range of item 1 must be 2. Between 1001 to 2000 KM ( 1.000 + 600 = 1.600). But if I select the Data Filter from 01/jan to 18/feb the range must be 3. Above 2001 KM (1.000 + 600 + 3.000 = 4.600).

 

 

Data Base
ItemDateAmount KM
101/jan1.000
202/feb500
303/feb600
404/feb800
105/feb600
212/feb750
118/feb3.000
220/feb1.200
324/feb1.600
426/feb1.000
401/mar1.600

 

The graphic I'm trying to create is something like this:

Range Date.PNG

 

Thanks!

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.Create a new table to record your range of amount KM.

1.PNG

2.Create a measure to calculate the changed amount KM.

Val = var t=SUMMARIZE('Data Base','Data Base'[Item],"t7",CALCULATE(if(SUM('Data Base'[Amount KM])<=MAX('Table1'[Column2]) && SUM('Data Base'[Amount KM])>MIN('Table1'[Column3]),1)))

        return

        COUNTX(t,[t7])

3.Create a Table visual and add the [Val] and [Column1] field.2.PNG

4.Create a Slicer visual and add the [Date] filed.

3.PNG

Now you can see the result.

4.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/eln6cm74539n2kl/Variable-Range-based-on-date-filter.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-danhe-msft
Employee
Employee

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.Create a new table to record your range of amount KM.

1.PNG

2.Create a measure to calculate the changed amount KM.

Val = var t=SUMMARIZE('Data Base','Data Base'[Item],"t7",CALCULATE(if(SUM('Data Base'[Amount KM])<=MAX('Table1'[Column2]) && SUM('Data Base'[Amount KM])>MIN('Table1'[Column3]),1)))

        return

        COUNTX(t,[t7])

3.Create a Table visual and add the [Val] and [Column1] field.2.PNG

4.Create a Slicer visual and add the [Date] filed.

3.PNG

Now you can see the result.

4.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/eln6cm74539n2kl/Variable-Range-based-on-date-filter.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

That's excactly what I need! Thank you !

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors