Bar chart including dates before the slicer date

[Anonymous]

Hello all!

I need to build a bar chart, which marks the last data date of a date slicer, but I want it to show not only the date of that filtered day, but also data up to 21 days prior to that slicer date. Created the following measure but it only shows me the current filter day.

I show you what i would like to get.

Can somebody help me?

Thank you very much!

measure = 

calculate(sum('Table1'[Value]),ALLSELECTED('Table1'[Date]),DATESBETWEEN('Table1'[Date],

lastdate(DATEADD('Table1'[Date],-21,DAY)),lastdate(DATEADD('Table1'[Date],-0,DAY))))

[Ashish_Mathur]

Hi,

See if this solution works - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have a try.

Create a calendar table and put the dates in the calendar table into the slicer.

Table 2 = CALENDAR(DATE(2022,1,1),DATE(2022,5,31))

Then create measures.

measure_beforedate =
VAR _sel =
    SELECTEDVALUE ( 'Table 2'[Date] )
RETURN
    IF ( MAX ( 'Table'[date] ) <= _sel, 1, 0 )

Measure_values =
SUMX (
    FILTER ( ALL ( 'Table' ), 'Table'[date] <= SELECTEDVALUE ( 'Table 2'[Date] ) ),
    'Table'[value]
)

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi @Anonymous , 

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure =
VAR _choosedate =
    SELECTEDVALUE ( 'Table'[date] )
VAR _21daysbefore =
    DATEADD ( 'Table'[date], -21, DAY )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= _21daysbefore
                && 'Table'[date] <= _choosedate
        )
    )

If I have misunderstood your meaning, pelase provide more details with desired output and your pbix file without privacy information.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Thanks for your help.

I need just what you sent me, but that a stacked bar appeared for each date, and I only select a single date, that is, if I select April 20, I need a bar to appear for each day of the previous ones until April 20 april.

Thanks!

[Anonymous]

Someone who can help me, please?

[Anonymous]

Hello, dies this help you with your problem 😊? @Anonymous 

The Slicer is a BEFORE slicer.
The _MaxDateMinus21 returns 25-01-2020, so any date before that is not counted

[Anonymous]

Hello,

Thanks for your reply, but is not that I need. I need to make it with a dropdown slicer.

Thanks 🙂 

[Anonymous]

Why is the dropdown slicer a must 🤔?

[Anonymous]

Design need 🙂