Slider - Does not take into consideration the data range selected

[kabra_ashish]

I have a date table with dates between Jan 1 2019 and Dec 31 2020. This date table is connected with a fact table which hosts the amounts and has a 1:Many relationship between them.

I created a slicer visual (slider) with the dates between Jan 1 2020 to February 11 2020 (using the data table). 

I need a sum of all the amounts using the above slicer. As it has a 1:many relationship I have used Calculate(sum(table.amount)) to sum up the values for the respective date.

Unfortunately it totals up all the data in the fact table. Any idea why does it total up all the values from the fact table and not the date range selected in  the slider?

[v-gizhi-msft]

Hi,

After my test, it works well here:

So for your issue, please check your relationship.

Please share some sample data and relationships you created and linked column if convenient for me to help you further.

Best Regards,

Giotto Zhi

[kabra_ashish]

Thanks Giotto.

The data model is exactly the same like what you have. Upload is disabled for me at the moment. I will try try to get the snapshot later.

Here is the scenario:

- The calendar table you have must have all the dates between Jan 1 2019 to Dec 31 2020.

- The other table "Table" has dates and values for dates between Jan 1 2019 to March 10 2020.

- Create a slider which shows dates from Jan 1 2020 to March 2020 (assume I want audience to see only current years data).

- Can you tell me whether the sum of values you get when you have Jan 1 2020 to March 10 2020 selected in the data range is correct?

For me when I select January 2 2020 to March 2020 in the slicer it shows the correct data. But when Jan 1 2020 to March 10 2020 is selected it actually sums up all the values in the table "Table" which means it includes data for 2019 as well. Basically the starting date is creating a problem for me.

It is really strange to me. I must be missing some logic here. Any help is highly appreciated!

[v-gizhi-msft]

Hi,

Please check this:

pbix 

And could you please share your sum measure and relationships setting screenshot with me?

Thanks!

Best Regards,

Giotto Zhi

[kabra_ashish]

Upload is still disabled for me. Sorry. But I have attached a screenshot.

Can you make a tiny edit to your file. Just filter with "is on or after 01/01/2020" in the visual level fiter (for slider visual) and select date range as Jan-01-2020 to Dec-31-2020 because I do not want the audience to go to prior years data.

Now if you look at the calculation it actually sums up the entire fact table and not the slider date range (it works well when selecting between Jan-02-2020 to Dec-31-2020 but not from Jan-01-2020)

[v-gizhi-msft]

Hi,

Please try to add this filter to the Card visual instead of Slicer.

And it works well when you choose 2020/1/1:

Best Regards,

Giotto Zhi

[kabra_ashish]

Thank you Giotto! This helps.

Question - But why do we need a date filter in the card visual? As it has 1:Many relationship the slicer when selected between 01/01/2020 - 12/31/2020 should show data only for this period and not the entire fact table. I don't understand why is this filter needed in the card visual at all. 

[v-gizhi-msft · Accepted solution]

Hi,

This is because as you said in your orginal post, you do not want the audience to go to prior years data.

So if you do not apply this filter to the card visual, when end users select the start date in 2019 in date slicer, the card will show the total sum of values including data in 2019.

If you using other visual such as table visual, it is the same principle.

For example, when you choose the start date from 2019/1/1, the table and card visual still show the data that start from 2020/1/1, Which will protect the data in 2019.

Best Regards,

Giotto Zhi

[amitchandak]

It should work if this is based column and date join in not inactive

Calculate(sum(table[amount]))

Can you share relation diagram and if amount is calculated field share its calculation