Forum Discussion

sakshikaul's avatar
sakshikaul
Icon for Helper III rankHelper III
5 years ago
Solved

Graph output not showing Correct

HI, I want in the following graph when I select year as 2021(current year) then it should only show values of all the months of current year ie Jan and feb for 2021. And automatically previous year...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi sakshikaul ,

    In order to make the data output correct, I created a table to store the 12 months from January to December. Associate it with the month column of the DBALLSTAT table, and set the filtering direction to single. Then modify the measure:

    previous year = 
var Selectvalue =    
CALCULATE(
        [SelectYear],
        FILTER(
            ALL('DBALLSTAT'),
            'DBALLSTAT'[Eco_year] = MAX('DBALLSTAT'[Eco_year])-1
            && DBALLSTAT[Month] = MAX(DBALLSTAT[Month])
        )
    )
var notselect = 
    CALCULATE(
        [SelectYear],
        FILTER(
            ALL('DBALLSTAT'),
            'DBALLSTAT'[Eco_year] = MAXX(ALL(DBALLSTAT),'DBALLSTAT'[Eco_year])-1
            && DBALLSTAT[Month] = MAX('Month'[Column1])
        )
    )
return 
    IF(
        ISERROR(ALLSELECTED(DBALLSTAT[Eco_year])),
        notselect,
        IF(
            ALLSELECTED('DBALLSTAT'[Eco_year])=2021,
            notselect,
            Selectvalue
        )
    )

    In my sample data, I have successfully fulfilled your requirements, please check the PBIX file for more details.

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi sakshikaul ,

I added a condition about the date ie DBALLSTAT[Date] to the formula, you try to see if it can get the result you want. If it is unsuccessful, please provide some sample data and relationships between tables.  

MaxYear1_HAWB = 
If(
  SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
  CALCULATE(
    SUM(DBALLSTAT[Profit])/1000,
    FILTER(
      (DBALLSTAT),
      DBALLSTAT[Eco_year]=MAX(DBALLSTAT[Eco_year])
      && DBALLSTAT[Date] <= EOMONTH(TODAY(),-1)
    ),
    FILTER(
      Group_Logic,
      Group_Logic[Load Type]="HAWB"
    )
  )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

 

  • sakshikaul's avatar
    sakshikaul
    Icon for Helper III rankHelper III
    5 years ago

    Hi,

    Earlier on selecting 2021 filter the data was not getting filtered for 2021. It was showing all the months for 2021 ie jan to dec and same for 2020

    but now I have solved one issue like now on selecting year =2021 I am getting data for jan and feb only which is correct but for 2020 I am not getting values. please help 

    Expression written for current Year

    Maxyear1_hawb=

    var summaryA=MAX(DBALLSTAT[Eco_year]) var summaryB=If(
    SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
    CALCULATE(
    SUM(DBALLSTAT[Profit])/1000,
    FILTER(
    (DBALLSTAT),
    DBALLSTAT[Eco_year]=summaryA
    ),
    FILTER(
    Group_Logic,
    Group_Logic[Load Type]="HAWB"
    )
    ))
    return summaryB

     

    expression for prev year

    PrevYear1_HAWB = var Summary_hawb_prev=MAX(DBALLSTAT[Eco_year])-1 var summary_hawb_prevB= If(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",CALCULATE(SUM(DBALLSTAT[Profit])/1000,FILTER((DBALLSTAT),DBALLSTAT[Eco_year]=Summary_hawb_prev),FILTER(ALL(Group_Logic),Group_Logic[Load Type] ="HAWB"))) return Summary_hawb_prev

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi sakshikaul ,

      Modify the measure PrevYear1_HAWB:

      PrevYear1_HAWB = 
var Summary_hawb_prev = MAXX(ALL(DBALLSTAT),DBALLSTAT[Eco_year])-1 
var summary_hawb_prevB = 
  If(
    SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
    CALCULATE(
      SUM(DBALLSTAT[Profit])/1000,
      FILTER(
        (DBALLSTAT),
        DBALLSTAT[Eco_year] = Summary_hawb_prev
      ),
      FILTER(
        ALL(Group_Logic),
        Group_Logic[Load Type] = "HAWB"
      )
    ) 
  ) 
return Summary_hawb_prev

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

      Best Regards,
      Winniz

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

      • sakshikaul's avatar
        sakshikaul
        Icon for Helper III rankHelper III
        5 years ago

        HI,

        If I am not selecting any year then I am getting the correct result but if I am selecting any year then I am not getting the result...I want on selection of year and non selection of year (both) I want values for current year and previous year. 

        for eg- if I selected year=2018 then the values for previous year should also reflect like 2018 and 2017( previous year)

        and if I am not selecting any year then values of max (year) and max(year)-1 should be reflected in graph( this is working fine now)

        On selection of year=2021 not getting the desired result ie values for 2020 should also be reflected in graph

        If I not selecting any year getting correct result