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