Show only current year in bar chart

[Alicetemitope]

Hi Community, I hope you are all doing great,
I am working on a report where I have to show actuals and target in one chart. I successfully did this but it shows for monthyear in the dataset in the chart like in fig 1. when I put a filter to show the actual and target for the whole of the current year, it only shows from jan-mar like in fig 2. what I would like is something like in fig 3. can you help please. below is the dax code I used;

Actual and Projection =
VAR LastCostDay =
CALCULATE(
MAX(‘Usage Details’[Date]),
ALL()
)
VAR Actualconsumption = [New consumption YTD]

VAR Result =
IF(
MAX(DIM_Date[Date]) > LastCostDay,
[New Projections YTD],
Actualconsumption
)

RETURN
Result

I tried doing

Actual and Projection =
VAR LastCostDay =
CALCULATE(
MAX(‘Usage Details’[Date]),
ALL()
)
VAR Actualconsumption = [New consumption YTD]

VAR Result =
IF(
MAX(DIM_Date[Date]) > LastCostDay,
[New Projections YTD],
Actualconsumption
)

RETURN
Calculate (Result, (DIM_Date[Year]) = Year(Today()))

still it didn't work. I would be glad if you can helpfig 2fig 3fig 1

Thanks in advance for your help

[amitchandak]

@Alicetemitope , Try with filter

RETURN
Calculate (Result, filter(DIM_Date, DIM_Date[Year]) = Year(Today()) ) )

[Alicetemitope]

Hi @amitchandak thanks for your response. it still didn't work. Here is a link to my pbix if you can help please. Thanks

https://drive.google.com/file/d/1ZttFjh7PcSYmztJhgropfM0bIX6nO6J0/view?usp=share_link

[v-yueyunzh-msft · Accepted solution]

Hi , @Alicetemitope 

Thanks for your sample pbix file .

I test it in my side , the result is very strange for the measures return. In my thought , the issue is casued by the

"TOTALYTD()" function in  [New Projections YTD] measure .

I have tested it many times with me, and there may be a problem with the filter context translation in this function. And you want to get the year-to-date data, we can try to replace another function for the calculation.

New Projections YTD 2 = 
var _cur_date = MAX('DIM_Date'[Date])
var _t =FILTER( ADDCOLUMNS( ALL('DIM_Date'[Date]),"v" , [New Projections]) , YEAR([Date]) = YEAR(_cur_date) && [Date]<= _cur_date)
return
SUMX(_t,[v])

Then we can modify the measure:

Actual and Projection = 
VAR LastCostDay = 
CALCULATE(
    MAX(DIM_Date[Date])
)
VAR CumulativeConsumption = [New consumption YTD]
VAR CumulativeProjection = [New Projections YTD]
VAR selectedyear = SELECTEDVALUE(DIM_Date[Year])
VAR Currentmonth = MONTH(TODAY())

var BetterYTD = 
    VAR __Date = MAX(DIM_Date[Date])
    VAR __Year = MAX('DIM_Date'[Year])
    VAR __Table = FILTER(ALL(DIM_Date),[Date] <= __Date && [Year] = __Year)
RETURN
    SUMX(__Table,[New Projections])

VAR Result = IF(selectedyear < YEAR(TODAY()), [New consumption YTD], IF( selectedyear <= YEAR(TODAY()) && SELECTEDVALUE(DIM_Date[Month]) < Currentmonth, [New consumption YTD],[New Projections YTD 2]))


RETURN
IF(selectedyear>YEAR(TODAY()),BLANK() ,Result)

Then we can get this result :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Alicetemitope]

thanks so much. it worked