Adding Target Line for 2023 Cumulative Costs in Power BI Visual

[manish-tripathi]

Hello Power BI Community,

I am seeking assistance with a visual I've created to compare cumulative costs for the years 2022 and 2023.

For the visual, I have developed the following measure for cumulative costs:

```DAX
Cumulative Costs =
CALCULATE(
SUM('Cost 1'[Amount EUR]),
FILTER(
ALL('Calendar'[Date]), // Remove the filter context from the Calendar table
YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date])) &&
MONTH('Calendar'[Date]) <= MONTH(MAX('Cost 1'[Date]))
)
)
```

This measure is placed on the Y-axis, with the Calendar Month on the X-axis, and the Calendar Year as the legend.

My query is about incorporating a target line for 2023 cumulative costs into this visual. Currently, the use of the legend for differentiating between the years is preventing me from adding a secondary Y-axis. Is there a way to work around this issue? Should I create a separate measure to facilitate the addition of the target line?

I have attached a sample of the data and a screenshot of the visual for your reference.

Any guidance you can provide would be greatly appreciated.

Sample Visual and sample Dataset Attached:

https://drive.google.com/drive/folders/1IOviSSW_LKRWuyKH_R7Gh6RaDDj6_zZV?usp=sharing 

Best Regards,
Manish Tripathi

[wdx223_Daniel]

1 mark your calendar table as date table, and add a new column of YearMonth, then put it in the X axis

2 

2022 Costs 1 = 
CALCULATE(
    SUM('Cost 1'[Amount EUR]),
    DATEADD(DATESYTD('Calendar'[Date]),-1,YEAR)
)

2023 Costs 1 = 
CALCULATE(
    SUM('Cost 1'[Amount EUR]),
    DATESYTD('Calendar'[Date]),YEAR('Calendar'[Date])=YEAR(TODAY())
)

TargetLine = CALCULATE(SUM('Cost 2'[Target]),DATESYTD('Calendar'[Date]),YEAR('Calendar'[Date])=YEAR(TODAY()))

[manish-tripathi]

Currently my metric has been further modified to interact with the year and month slicers on the Visualization page

Updated DAX Measure: ```LTM Costs Cumulative =
VAR MaxDate = MAX(Dates[Date])
VAR MinDate = EOMONTH(MaxDate, -12)

VAR Result =
IF(
HASONEVALUE('Previous Dates'[MonthYear]) &&
MAX('Previous Dates'[Date]) <= MaxDate &&
MIN('Previous Dates'[Date]) > MinDate,
CALCULATE(
[Cumulative Costs],
FILTER(
ALL(Dates),
Dates[Date].[Month] = VALUES('Previous Dates'[Date].[Month]) &&
Dates[Date].[Year] = VALUES('Previous Dates'[Date].[Year])
)
),
BLANK()
)

RETURN
Result``` where it uses the metric created earlier to SHOW Cumulative revenue for LTM, however the way you have suggested, it is not giving the desired output

I need something that is made available through the below link but for free
https://pbivizedit.com/gallery/line-chart

This allows multiple line charts