Forum Discussion

apatwal's avatar
apatwal
Helper 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 respective year to the current date, while Full Year flag shows the data from 1st jan to 31st dec of respective year.

 

I have my data from year 2020, 2021 and 2022 and want to display the revenue for each year on different columns in table. To display each year revenue on different columns I am using below dax

2020 Revenue =
CALCULATE(
SUM(data[Revenue]),
FILTER(
data,
YEAR(data[Invoice Date]) = 2020
)
)
Same for other years by changing year in above dax.
 
Need help in creating Flag meachanism. Appreciate your help!
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]
)

11 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    apatwal 

    You allways come up with nice ideas. Will try to play around with it once I return back to office. Thank you

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
    Super 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

  • tamerj1's avatar
    tamerj1
    Community 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]
)

    • apatwal's avatar
      apatwal
      Helper III
      4 years ago

      Hi tamerj1 

       

      Thanks for your reply!

      I understand your approach. I am building matrix visual and want to show data for 2022 and 2021 revenue only in matrix visual (or table visual) with change in revenue (2022 rev - 2021 rev) and revenue change% columns based on location.

       

      While in chart, I want to display revenue for each years (2020, 2021 and 2022) something like year over year.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        apatwal 
        You can filter the report to include the years you want that is not an issue. 
        How do you calculate the %Revenue Change? Location is no problem as well, you can add as slicer. Can you share ascreenshot of the expected report?