YTD 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...

DAX

DAX Commands

DAX Commands and Tips

Help Requested

Help required

tamerj1

4 years ago

Hi 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 
    Result

The measure to be used in the visual

Selected revenue 2020 = 
IF (
    SELECTEDVALUE ( Selection[Flag Selection] ) = "YTD flag",
    [Invoice revenue 2020 YTD],
    [Invoice revenue 2020]
)