Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Use date slicer as limit date

Hi all, I need a column chart to show sales, the only problem that I´m having is that I have a slicer for date, so I need to use that slicer as the limit date, I need the visual to show sales for every other dates until the date I´m filtering.

 

Thanks a lot

 

 

Need Help
Show and Tell
Tips and Tricks
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    First, create a disconnected calendar table as slicer.

    DimDate = ADDCOLUMNS(CALENDAR("01/01/2018","31/12/2019"),"Month - Year", FORMAT([Date],"mm - yy"),"SortMonth",FORMAT([Date],"yyyymm"))

    Them create a mesure like this:

    Sum_Sales = 
CALCULATE (
    SUM( Sales[Sales] ),
    FILTER (
        ALL ( Sales[Date] ),
        Sales[Date] <= MAX ( DimDate[Date] )
            
    )
)

    Sample .pbix

     

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

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.


     

    Unless you filter slicer will show all dates. If you want to show nothing then try

     

    if(isfiltered(Date[Date]), Sum(Table[Sales]), Blank())

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, I have slicer for year and month, so what I´m looking is a measure that shows values until that date when using a year and montly slicer in a visual such as a column chart. Thanks a lot

  • Angith_Nair's avatar
    Angith_Nair
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi Anonymous ,

    You can use the date slicer and make that date slicer to before property as shown below.

    So when you pick a date in the slicer, the values will be shown until that date. Please note that the date which you picked will also be included. So basically it will be like values<= Dates which you picked...

    If this helps then kindly mark it as a solution. Appreciate with Kuddos.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, this is exactly what I need, but I have slicer for year and month not a date slicer, so what I´m looking is a measure that shows values until that date when using a year and montly slicer. Thanks a lot

      • Angith_Nair's avatar
        Angith_Nair
        Icon for Continued Contributor rankContinued Contributor
        5 years ago

        Create a separate Calendar table having columns year and months. Make a relationship with the main table. Plot month and year in the slicer from the Calendar table. And try to use this measure..

        Measure =
CALCULATE (
    SUM ( Table[Values] ),
    ALL ( Calendar ),
    Table[Values] < SELECTEDVALUE ( Calendar[Year] )
        && Table[Values] < SELECTEDVALUE ( Calendar[Month] )
)
  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    First, create a disconnected calendar table as slicer.

    DimDate = ADDCOLUMNS(CALENDAR("01/01/2018","31/12/2019"),"Month - Year", FORMAT([Date],"mm - yy"),"SortMonth",FORMAT([Date],"yyyymm"))

    Them create a mesure like this:

    Sum_Sales = 
CALCULATE (
    SUM( Sales[Sales] ),
    FILTER (
        ALL ( Sales[Date] ),
        Sales[Date] <= MAX ( DimDate[Date] )
            
    )
)

    Sample .pbix

     

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