Forum Discussion
YTD Flag using DAX
- 4 years ago
Hi apatwal
Final solution is as follows
Create a disconnected selection tableSELECTION = 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 ResultThe measure to be used in the visual
Selected revenue 2020 = IF ( SELECTEDVALUE ( Selection[Flag Selection] ) = "YTD flag", [Invoice revenue 2020 YTD], [Invoice revenue 2020] )
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.
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?
- apatwal4 years agoHelper III
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