Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Bar chart including dates before the slicer date

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))))

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello,

       

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

       

      Thanks 🙂 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Why is the dropdown slicer a must 🤔?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Someone who can help me, please?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.