Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 )
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |