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 ,

Modify the measure:

previous year = 
var Selectvalue =    
CALCULATE(
        [SelectYear],
        FILTER(
            ALL('DBALLSTAT'),
            'DBALLSTAT'[Eco_year] = MAX('DBALLSTAT'[Eco_year])
            && 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(DBALLSTAT[Month])
        )
    )
return IF(ISFILTERED('DBALLSTAT'[Eco_year]),Selectvalue,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

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

HI,

If I am writing above expresssion again I am not getting previous Year comparison in chart and also I am not getting the correct value.

 

I am not geeting the correct value. If I am not selecting any year then also I am getting value for 2021 march which is not correct. I have data for 2021 for jan and feb only.

 

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    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,

    I am getting an expected output if I am selecting year=2021 as shown below....

     

    but if I want if I select year =2020 then I want the comparison for current year anr previous year ( basically I want on dynamic selection - whichever year user will select the data should be reflected for the selected year and one year previous to the currently selected year )

    In below case if I select year=2020 then only 2020 data is shown, whereas data for 2019 should also be displayed.

     

     

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

    Hi 

    , I am getting an expected output if I am selecting year=2021 as shown below

     

     

    .... but my requirement was I want if I select year =2020 then I want the comparison for current year and previous year

    ( basically I want on dynamic selection - whichever year user will select the data should be reflected for the selected year and one year previous to the currently selected year ) In below case if I select year=2020 then only 2020 data is shown, whereas data for 2019 should also be displayed.   please help

     

     

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

    Hi sakshikaul ,

    Yes. My previous reply gave the output you expected.

    Please see Message 33 for details.

    Best Regards,
    Winniz