Forum Discussion

Alicetemitope's avatar
Alicetemitope
Frequent Visitor
3 years ago
Solved

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 ...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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