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
Nilsoo
Frequent Visitor

Time Range Filter: How to keep filtered values?

Hi,
I have the following problem I have a data set containing Sales values of a specific date (my short table below should clarify the topic). I would like to use a time range filter that says how the total sales of the beginng of a period and at the end of a period is. I want to display the overall total sales and the sales increase/decrease of that time range on one page.
If I adjust the time range and set it to 2.1 - 4.1, the value of the date 1.1 is filtered out so my total sales for 2.1 is 5 )instead of 25) and for 4.1 it is 45 (instead of 65). Anyone an idea how to solve this? 
Is there any possiblity surpressing

 

Date      Sales

1.1         20

2.1          5

3.1         25

4.1         15

5.1         10

1 ACCEPTED SOLUTION

So what you're looking for is a running total to that point in time?

 

So this would result to

Calculate( SUM(Sales);

Datesbetween(DateTable;Min(Date);Max(Date));

ALL(Date Table) Removes all filters on the date table 

)

 

This should provide a running total, if my datesbetween statement is wrong 

have a look at 

http://www.daxpatterns.com/cumulative-total/

View solution in original post

6 REPLIES 6
NielsDecoene
Helper I
Helper I

 

Calculate( SUM(Sales);Datesbetween(DateTable;Min(Date);Max(Date)))

You'd have to use a filter with multi select.

Basically you will have to use Calculate Statement with a Datesbetween to specify a range of dates:

https://msdn.microsoft.com/en-us/library/ee634557.aspx

 

If I missed something tag me plz 🙂

 

Hey,

thanks for your response.

Unfortunately, this is not quite what I am looking for. Let me clarify this.

On the same page I have on value that should show me the total sales upon an specific date e.g. 4.1 --> total sales = 65.

On this page I have another value which should show me the amount sales in the seletecd time range 2.1 - 4.1 --> #sales in time range = 45.

On the page there is the time range filter that allows me to adjsut the time period (see screenshot)as.PNG

 

To calculate the amount of sales in the selected time range is easy. It's also no problem to calculate the overall total sales by surpressing all filters. However, calculating the total sales upon a specific date seems to be tricky since the time range filter, sorts out all the data that is not in the time range.
If I want to know what is the total sales on 4.1 I kneed to know what was the sales on 1.1 to add it to the #sales in time range.


But I don't know how to get these information. Anyone?

 

So what you're looking for is a running total to that point in time?

 

So this would result to

Calculate( SUM(Sales);

Datesbetween(DateTable;Min(Date);Max(Date));

ALL(Date Table) Removes all filters on the date table 

)

 

This should provide a running total, if my datesbetween statement is wrong 

have a look at 

http://www.daxpatterns.com/cumulative-total/

Sorry but it is not working...

Nilsoo
Frequent Visitor

hey NilsDecoene,

 

thanks for introducing the Datesbetween function. I solved it now!
You need to create 2 measueres.

 

One that contains the all the Data up to the starting date of the filter and the second one that contains all data.

You need to apply the Datesbetween function for the first one. You set the start date on 01/01/1900 and the End Date to

min(Date).

 

Best.
Nils

Nilsoo
Frequent Visitor

With this solution you only need one meassure that gives you the running total:

max. Date = Calculate( COUNT([Sales]);

Datesbetween('Sales'[Date].[Date]; Max('Sales'[Date]); 01/01/2100);
ALL('Sales'[Date])
)

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.