Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Alicetemitope
Frequent Visitor

Show only current year in bar chart

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 2fig 3fig 3fig 1fig 1

 

Thanks in advance for your help

1 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 :

vyueyunzhmsft_0-1683083106057.png

 

 

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Alicetemitope , Try with filter

 

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

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

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 :

vyueyunzhmsft_0-1683083106057.png

 

 

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

thanks so much. it worked

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.