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.

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

     

     

     

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

      Hi sakshikaul ,

      The Year field you put in the slicer should come from a separate table. Because if you use DBALLSTAT[Eco_year], then you choose 2021, only the 2021 data will be filtered out.

       

      You can refer to the following sample data for modification.

       

      Measure = 
var MaxYear = MAXX(ALL('Table'),'Table'[year])
var SelectYear = 
    CALCULATE(
        SUM('Table'[value]),
        FILTER(
            'Table',
            'Table'[year] in { ALLSELECTED(Slicer[Year]), ALLSELECTED(Slicer[Year])-1 }
        )
    )
var NotSelect = 
    CALCULATE(
        SUM('Table'[value]),
        FILTER(
            'Table',
            'Table'[year] in { MaxYear, MaxYear-1 }
        )
    )
return 
    IF(ISFILTERED(Slicer),SelectYear,NotSelect)

       

           

      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,

        You want me to create duplicate of DBALLSTAT[Eco_year] for slicer?

        and also,

        if I am suppose to create duplicate of dballstat(eco_year)  for slicer with the table name ecoYeartable

        then,
        Measure =
        var MaxYear = MAX(ALL('ecoYeartable'),'ecoYeartable'[year])
        var SelectYear =
        CALCULATE(
        SUM('Table'[value]),----> which value ?? 

        CALCULATE(
        SUM(DBALLSTAT[Profit])/1000, FILTER(
        Group_Logic,
        Group_Logic[Load Type]="HAWB",
        FILTER(
        'Table',
        'Table'[year] in { ALLSELECTED(Slicer[Year]), ALLSELECTED(Slicer[Year])-1 }
        )
        )
        var NotSelect =
        ('CALCULATE(
        SUM(DBALLSTAT[Profit])/1000,),
        FILTER(
        Group_Logic,
        Group_Logic[Load Type]="HAWB",

        FILTER(
        'Table',
        'Table'[year] in { MaxYear, MaxYear-1 }
        )
        )
        return
        IF(ISFILTERED(Slicer),SelectYear,NotSelect)

         

        THIS IS HOW THE EXPRESSION WILL BE WRITTEN ?