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]
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi apatwal 
Here is a sample file https://www.dropbox.com/t/pgHYjhGndQZwRb4N
I suppose by "Current Date" you mean today. Also I did not fully understand why the year number is hard codded. Are you using card visuals?
Anyway, the aproach should be the same. Craete a disconnected selection table

SELECTION = SELECTCOLUMNS ( { "YTD Flag", "Full Year Flag" }, "Flag Selection", [Value] )

The basic two measure

Total Revenue = SUM ( Data[Revenue] )
YTD Revenue = 
VAR CurrentYear = 
    YEAR ( MAX ( 'Date'[Date] ) )
VAR Result =
    IF ( 
        [Total Revenue] > 0,
        CALCULATE (
            [Total Revenue],
            REMOVEFILTERS ('Date' ),
            Data[Invoice Date] <= TODAY ( ),
            Data[Invoice Date] >= DATE ( CurrentYear, 1, 1 )
        )
    )
RETURN
    Result

And the measure to use in visual 

Selected Revenue = 
IF (
    SELECTEDVALUE ( SELECTION[Flag Selection] ) = "YTD Flag", [YTD Revenue],
    [Total Revenue]
)

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago

Omit it, this is for fun only; an imporved measure which can produce correct date at the end of Feburary (assume today is 28 Feb, 2022).