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,

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.