Forum Discussion

apatwal's avatar
apatwal
Icon for Helper III rankHelper III
4 years ago
Solved

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's avatar
    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]
)
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

apatwal , You can create a flag like this in date table

if( format([Date], "MMDD") < = format(today(), "MMDD") ,1,0)

 

 

Prefer date table in case of time intelligence