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 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
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]
)

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    In theory, you can unify both to DATESYTD() by manipulating the evaluation context for it. Here's the trick,

     

  • 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?

      • apatwal's avatar
        apatwal
        Helper III
        4 years ago

        tamerj1 

         

        Below is snapshot for matrix visual (ignore Margin% as of now).

         

         

        Yes, Location can be filter through slicer.

        Revenue change % = (2022 rev - 2021 rev)/2021 rev

         

        While in chart Revenue should be visible as below which should gradually grow as we have subsequent months data:

        Blue : 2022 Revenue

        Gray : 2021 Revenue

        Orange : 2020 Revenue

         

         

  • CNENFRNL's avatar
    CNENFRNL
    Community 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).