Forum Discussion

Helper 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

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.

Graph output not showing Correct.pbix368 KB

v-kkf-msft

Community 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.

Graph output not showing Correct.pbix362 KB

sakshikaul

Helper 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 ?

v-kkf-msft

Community Support

5 years ago

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
Helper III
5 years ago
Still not getting the output.. getting following above error
- v-kkf-msft
  Community Support
  5 years ago
  Hi sakshikaul ,
  
  Change the type of column Slicer[Year] to the same type as column DBALLSTAT[Eco_year].

sakshikaul
Helper 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
  Community 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.
sakshikaul
Helper III
5 years ago
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)

Forum Discussion

Graph output not showing Correct

Related content

New Card visual settings are messed up in service while showing correct output in desktop app

Correcting Data

Box plot not showing correct mean

Dax Formula correction help

Dax correction