Forum Discussion
Helper IIIGraph output not showing Correct
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.
Helper IIIHI,
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
Community SupportHi 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
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 ?
- v-kkf-msft
Hi sakshikaul ,
Try this formula:
Measure = var MaxYear = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year]) var SelectYear = If( SELECTEDVALUE('Summary Field Selection'[Field Value])="GP", CALCULATE( SUM(DBALLSTAT[Profit])/1000, FILTER( DBALLSTAT, DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Year]),ALLSELECTED(Slicer[Year])-1} ), FILTER( Group_Logic, Group_Logic[Load Type]="HAWB" ) ) ) var NotSelect = If( SELECTEDVALUE('Summary Field Selection'[Field Value])="GP", CALCULATE( SUM(DBALLSTAT[Profit])/1000, FILTER( DBALLSTAT, DBALLSTAT[Eco_year] in { MaxYear, MaxYear-1 } ), FILTER( Group_Logic, Group_Logic[Load Type]="HAWB" ) ) ) 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- sakshikaul
Still not getting the output.. getting following above error
- sakshikaul
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
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.
- sakshikaul
Hi,
below measure is also giving me an error...please help?
Measure =
var MaxYear = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year])
var SelectYear =
If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
CALCULATE(
SUM(divide(DBALLSTAT[Profit]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Year]),ALLSELECTED(Slicer[Year])-1}
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB",
SUM(DBALLSTAT[CBM]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Year]),ALLSELECTED(Slicer[Year])-1}
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB",
)
)
)
var NotSelect =
If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
CALCULATE(
SUM(DBALLSTAT[Profit])/1000,
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in { MaxYear, MaxYear-1 }
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB"
)
)
)
return
IF(ISFILTERED(Slicer),SelectYear,NotSelect)