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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.