Forum Discussion

Raman3456's avatar
Raman3456
Helper II
2 years ago
Solved

Select required dates from slicer selection

Hello Eveyone ,

 

I have a data customer , creation date , Revenue.

 

i want one table which if I select a creation date in my slicer ---- table data would show ( mar 5 2024 If I select .... I want to showcase table last 4 months data along with ... 

 

( 05-03-204, 04-03-2024,03-03-2024,02-03-2024,01-03-2024, 05-02-024, 04-02-2024,03-02-2024,01-02-2024 ..... till last 4 months only )

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Raman3456 ,

    The Table data is shown below:

    Please follow these steps:

    1.Create a date table for a slicer.

     

    Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))

     

     

    2.Use the following DAX expression to create a measure

     

    MEASURE =
VAR _dateForSlicer =
    MAX ( 'Calendar'[Date] )
VAR _1MonthAgo =
    EDATE ( _dateForSlicer, -1 )
VAR _2MonthAgo =
    EDATE ( _dateForSlicer, -2 )
VAR _3MonthAgo =
    EDATE ( _dateForSlicer, -3 )
VAR _table1 =
    CALENDAR (
        DATE ( YEAR ( _dateForSlicer ), MONTH ( _dateForSlicer ), 1 ),
        _dateForSlicer
    )
VAR _table2 =
    CALENDAR (
        DATE ( YEAR ( _1MonthAgo ), MONTH ( _1MonthAgo ), 1 ),
        DATE ( YEAR ( _1MonthAgo ), MONTH ( _1MonthAgo ), DAY ( _dateForSlicer ) )
    )
VAR _table3 =
    CALENDAR (
        DATE ( YEAR ( _2MonthAgo ), MONTH ( _2MonthAgo ), 1 ),
        DATE ( YEAR ( _2MonthAgo ), MONTH ( _2MonthAgo ), DAY ( _dateForSlicer ) )
    )
VAR _table4 =
    CALENDAR (
        DATE ( YEAR ( _3MonthAgo ), MONTH ( _3MonthAgo ), 1 ),
        DATE ( YEAR ( _3MonthAgo ), MONTH ( _3MonthAgo ), DAY ( _dateForSlicer ) )
    )
VAR _dateTable =
    UNION ( _table1, _table2, _table3, _table4 )
RETURN
    SUMX ( FILTER ( 'Table', 'Table'[Creation Date] IN _dateTable ), [Revenue] )

     

    3.Final output

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Raman3456 ,

    The Table data is shown below:

    Please follow these steps:

    1.Create a date table for a slicer.

     

    Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))

     

     

    2.Use the following DAX expression to create a measure

     

    MEASURE =
VAR _dateForSlicer =
    MAX ( 'Calendar'[Date] )
VAR _1MonthAgo =
    EDATE ( _dateForSlicer, -1 )
VAR _2MonthAgo =
    EDATE ( _dateForSlicer, -2 )
VAR _3MonthAgo =
    EDATE ( _dateForSlicer, -3 )
VAR _table1 =
    CALENDAR (
        DATE ( YEAR ( _dateForSlicer ), MONTH ( _dateForSlicer ), 1 ),
        _dateForSlicer
    )
VAR _table2 =
    CALENDAR (
        DATE ( YEAR ( _1MonthAgo ), MONTH ( _1MonthAgo ), 1 ),
        DATE ( YEAR ( _1MonthAgo ), MONTH ( _1MonthAgo ), DAY ( _dateForSlicer ) )
    )
VAR _table3 =
    CALENDAR (
        DATE ( YEAR ( _2MonthAgo ), MONTH ( _2MonthAgo ), 1 ),
        DATE ( YEAR ( _2MonthAgo ), MONTH ( _2MonthAgo ), DAY ( _dateForSlicer ) )
    )
VAR _table4 =
    CALENDAR (
        DATE ( YEAR ( _3MonthAgo ), MONTH ( _3MonthAgo ), 1 ),
        DATE ( YEAR ( _3MonthAgo ), MONTH ( _3MonthAgo ), DAY ( _dateForSlicer ) )
    )
VAR _dateTable =
    UNION ( _table1, _table2, _table3, _table4 )
RETURN
    SUMX ( FILTER ( 'Table', 'Table'[Creation Date] IN _dateTable ), [Revenue] )

     

    3.Final output

     

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