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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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()) ) )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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