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,

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 ?

 

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

I am not suppose to give a seperate calender in UI design. I want on selection of any Year... data for that selected year and corresponding previous year should get reflected in chart ie if current year selected= 2020 then current year and previous year =2019 data should be reflected in the chart 

 

and by default if nothing is selected then data for max(year) and max(year)-1 should be selected...

 

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

    Hi sakshikaul ,

    I understand what you think. But in desktop, the slicer will filter the data in the same table.

    That is, if you use DBALLSTAT[Eco_year] as the value of the slicer, when you select 2021, the desktop will first filter out the rows with the value of 2021 in the column Eco_year in the DBALLSTAT table. The measures you create will be calculated based on the filtered rows, which is why the data in 2020 will not be displayed when you choose 2021.