Forum Discussion
apatwal
Helper III
4 years agoYTD Flag using DAX
Hi I have a requirement to add a flag in my report. Flag has two values YTD Flag and Full Year. When YTD Flag is selected, it will show the values in the table in terms of Jan 1 of the respe...
- 4 years ago
Hi apatwal
Final solution is as follows
Create a disconnected selection tableSELECTION = SELECTCOLUMNS ( { "YTD Flag", "Full Year Flag" }, "Flag Selection", [Value] )Example measure
Invoice revenue 2020 = SUM ( 'Main table'[Invoice Revenue] )The YTD of a certain year (for example 2020 ) of the above measure would be
Invoice revenue 2020 YTD = VAR CurrentYear = 2020 VAR TodayDay = DAY ( TODAY () ) VAR TodayMonth = MONTH ( TODAY () ) VAR Result = IF ( SUM ( 'Main table'[Invoice Revenue] ) > 0, CALCULATE ( SUM ( 'Main table'[Invoice Revenue] ), FILTER ( 'Main table', 'Main table'[Invoice Date] <= DATE ( CurrentYear, TodayMonth, TodayDay ) && 'Main table'[Invoice Date] >= DATE ( CurrentYear, 1, 1 ) ) ) ) RETURN ResultThe measure to be used in the visual
Selected revenue 2020 = IF ( SELECTEDVALUE ( Selection[Flag Selection] ) = "YTD flag", [Invoice revenue 2020 YTD], [Invoice revenue 2020] )
tamerj1
Community Champion
4 years agoHi apatwal
Final solution is as follows
Create a disconnected selection table
SELECTION = SELECTCOLUMNS ( { "YTD Flag", "Full Year Flag" }, "Flag Selection", [Value] )Example measure
Invoice revenue 2020 = SUM ( 'Main table'[Invoice Revenue] )The YTD of a certain year (for example 2020 ) of the above measure would be
Invoice revenue 2020 YTD =
VAR CurrentYear = 2020
VAR TodayDay = DAY ( TODAY () )
VAR TodayMonth = MONTH ( TODAY () )
VAR Result =
IF (
SUM ( 'Main table'[Invoice Revenue] ) > 0,
CALCULATE (
SUM ( 'Main table'[Invoice Revenue] ),
FILTER (
'Main table',
'Main table'[Invoice Date] <= DATE ( CurrentYear, TodayMonth, TodayDay )
&& 'Main table'[Invoice Date] >= DATE ( CurrentYear, 1, 1 )
)
)
)
RETURN
ResultThe measure to be used in the visual
Selected revenue 2020 =
IF (
SELECTEDVALUE ( Selection[Flag Selection] ) = "YTD flag",
[Invoice revenue 2020 YTD],
[Invoice revenue 2020]
)