How to set one chart with full month view, while one day is filterred?

[KasiaG]

Hello Guys!

I've created a report and this have to be filterred by one slicer with the exact date selected (DD-MM-YYYY).

Once date is selected, every visualisation is filterred for this day, BUT I want a specific one to be filterred for whole month from the selected day. So, for example, if the user will choose 08.09.2022, I want this chart to show whole September.

It is a chart that show daily sales (not cumulative, single value of sales for each day), and this is created from Table with columns "Calendar Day" and "Sales".

I've tried with ENDOFMONTH, but unsuccesfull. 

This is how chart is look like when whole month is selected, and I want to look exact the same way when only one day from that month is selected:

And this is how it is look like after selecting one date from a slicer:

Thanks Guys in advance!

[Anonymous]

Hi  @KasiaG ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Table 2'[Date])
var _min=
DATE(
    YEAR(_select),MONTH(_select),1)
var _max=
EOMONTH(
    _min,0)
return
IF(
    MAX('Table'[Date]) >=_min&&MAX('Table'[Date]) <= _max,1,0)

Value =
var _select=SELECTEDVALUE('Table 2'[Date])
return
IF(
MAX('Table'[Date]) = _select,SUM('Table'[Rand]),BLANK())

2. Place [Flag]in Filters, set is=1, apply filter.

3. Visual  -- Format – X axis – Categorical

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[KasiaG]

Liu Yang,

I see your solution result in full month view in the aixle, but the value is only at the selected day.

I need the chart to contain full month view, including values from whole month when there will be filterred any day of that month. 

I know that I can simply just disconnect the chart from the filter, and create a new filter only for this chart. But would like to avoid that, to make the report more userfriendly.

[DataInsights]

@KasiaG,

See if this technique will meet your requirement:

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/