YTD Flag using DAX

[apatwal]

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!

[tamerj1 · Accepted solution]

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]

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]

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

[CNENFRNL]

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

[apatwal]

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]

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

@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

[apatwal]

Hi @tamerj1 

Did you have the chance to look into my last reply?

Let me know if you need any further information.

[tamerj1]

I'm Sorry @apatwal  Just saw your reply. I'll have a look at your reply soon and get back to you.

[amitchandak]

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

@apatwal 

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