topic Re: YTD Flag using DAX in DAX Commands and Tips

YTD Flag using DAX

apatwal — Tue, 29 Mar 2022 12:03:13 GMT

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!

Re: YTD Flag using DAX

tamerj1 — Thu, 24 Mar 2022 07:59:51 GMT

apatwal

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

Re: YTD Flag using DAX

amitchandak — Thu, 24 Mar 2022 08:19:08 GMT

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

Re: YTD Flag using DAX

tamerj1 — Thu, 24 Mar 2022 11:12:24 GMT

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

Re: YTD Flag using DAX

apatwal — Thu, 24 Mar 2022 12:03:23 GMT

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.

Re: YTD Flag using DAX

tamerj1 — Thu, 24 Mar 2022 12:42:08 GMT

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?

Re: YTD Flag using DAX

CNENFRNL — Thu, 24 Mar 2022 13:05:03 GMT

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

Re: YTD Flag using DAX

apatwal — Thu, 24 Mar 2022 13:15:03 GMT

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

Re: YTD Flag using DAX

CNENFRNL — Thu, 24 Mar 2022 13:40:00 GMT

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

Re: YTD Flag using DAX

apatwal — Mon, 28 Mar 2022 04:56:25 GMT

Hi tamerj1

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

Let me know if you need any further information.

Re: YTD Flag using DAX

tamerj1 — Mon, 28 Mar 2022 05:44:02 GMT

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

Re: YTD Flag using DAX

tamerj1 — Thu, 31 Mar 2022 07:50:42 GMT

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