Reply
Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

Date slicer does not filter histogram

Hi all,

 

I am new to power BI and I am trying to use a date slicer to filter an histogram.

I tried to use today's date in the date slicer in order to filter data on the last 3 months.

In the histogram I want to have the number of sales on the last 3 months distincly, for that I put the sales date on the axis.

However the date slicer is not working on the histogram, which is supposed to be filtered.

forum.png

My number of sales measure is as follow :

Nb actés = calculate (sum(Lots[Nombre_lots_actés]))

 

Do I need to change the formula?

 

Thank you in advance for your help!

Community Support Team
Posts: 1,357
Registered: ‎07-25-2018

Re: Date slicer does not filter histogram

Hi @Op37_alice

 

I have tested on my side as below picture, but not reproduce the same issue as you said. You may try to check your data type. If it is not your case, It’s better to share more about your data sample.

1.png

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

Re: Date slicer does not filter histogram

Hi @v-cherch-msft,

 

Thank you for your answer, I realised I didn't have the same date column on my slicer and histogram, so now it is working for one measure.

 

In fact I would like to have on the same histogram the number of items sold and the number of items unsold for the last 3 months. If I use the sale's date I obviously can't see the number of items unsold, is there a way to do that? 

 

Thank you

 

 

Community Support Team
Posts: 1,357
Registered: ‎07-25-2018

Re: Date slicer does not filter histogram

Hi @Op37_alice

 

You may try to create a calendar to link the date in your table. If it is not your case, please share some data sample and expected output.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

Re: Date slicer does not filter histogram

Hi @v-cherch-msft,

 

I have a calendar table linked to a column date in my item table. This column date returns, depending on the item's status, the sale's date if the item is sold or the date when the item was delivered to the store if it's unsold.

 

However, if the item unsold was delivered in january for example, it's not counted on the histogram because its date is not among the last 3 months...

I would like to see this item unsold on my histogram for the month of may, june and july, is there a way to do that?

 

Here is an example of data and what I expect :

forum 4.png

 

Thank you in advance for your help!

Community Support Team
Posts: 1,357
Registered: ‎07-25-2018

Re: Date slicer does not filter histogram

Hi @Op37_alice

 

You may try to create a measure as requested. For example:

Unsold =
COUNTROWS ( FILTER ( Table1, Table1[Status] = "Unsold" ) )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

Re: Date slicer does not filter histogram

Hi @v-cherch-msft,

 

I tried what you suggested but it doesn't show me the good result.

 

In this example I have in total 34 items, none of them has been sold, 33 of them has been added to the stock the 10/11/2016 and the last one the 26/06/2018.

My actualized date column is linked to a calendar table used in the slicer and on my axis.

Hence, I am supposed to have 33 items unsold in may, 34 in june, 34 in july and 34 in august.

However I can only see the item added the 26/06/2018, not the one before...

forum 7.pngforum 6.png

I tried to use allselected to not take into account the date filter but in that case i have all months on my axis not only the last 3 ones.

Frequent Visitor
Posts: 11
Registered: ‎08-14-2018

Re: Date slicer does not filter histogram

I managed to obtain the expected results of my measures by relating my two different dates to one calendar table using unactive joins:

forum 8.png

 

Here are the formulas of my measures :

Nb items sold = calculate (sum(Lots[Nb sold]); USERELATIONSHIP(Lots[DATEVENTE];Calendrier[Date]))

Nb items unsold= CALCULATE (Sum(Lots[Nb stock])-[Nb items sold]; USERELATIONSHIP(Lots[DATEMISEENCOM];Calendrier[Date]); FILTER(All(Calendrier); Calendrier[Date] <= MAX(Calendrier[Date])))

 

However on my histogram I have the list of all months and not only the last 3 ones:

forum 9.png

I have no problems with measure  "Nb items sold" alone.

 

How can I change the formula of measure "Nb items unsold" so that only the last 3 months are displayed on the histogram?