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

sakshikaul

Helper III

5 years ago

Hi,

Still getting an error in following expression

MaxYear3_HAWB_Gp/Vol = var MaxYear = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year])
var Select_Year =
If(
or(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",SELECTEDVALUE('Summary Field Selection'[Field Value])="Volume"),
DIVIDE(CALCULATE(SUM(DBALLSTAT[Profit]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] = max('DBALLSTAT'[Eco_year])
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB")),
CALCULATE(SUM(DBALLSTAT[ChglWt(KG)]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] = max('DBALLSTAT'[Eco_year])
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB"),0
)
)
)
var NotSelect =
If(
or(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",SELECTEDVALUE('Summary Field Selection'[Field Value])="Volume"),
DIVIDE(CALCULATE(SUM(DBALLSTAT[Profit]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in { MaxYear, MaxYear-1 }
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB")),
CALCULATE(SUM(DBALLSTAT[ChglWt(KG)]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in { MaxYear, MaxYear-1 }
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB"),0
)
)
)
return
IF(ISFILTERED('DBALLSTAT'[Eco_year]),Select_Year,NotSelect)

v-kkf-msft

Community Support

5 years ago

Hi sakshikaul ,

I create some sample data and then create the following measures.

MaxYear3_HAWB_Gp/Vol = 
var Select_Year =
    If(
        SELECTEDVALUE('Summary Field Selection'[Field Value]) = "GP"||SELECTEDVALUE('Summary Field Selection'[Field Value])="Volume",
        CALCULATE(
            DIVIDE(SUM(DBALLSTAT[Profit]),SUM(DBALLSTAT[ChglWt(KG)])),
            FILTER(
                DBALLSTAT,
                DBALLSTAT[Eco_year] = max('DBALLSTAT'[Eco_year])
            ),
            FILTER(
                Group_Logic,
                Group_Logic[Load Type]="HAWB"
            )
        )
    )
var NotSelect =
    If(
        SELECTEDVALUE('Summary Field Selection'[Field Value])="GP"||SELECTEDVALUE('Summary Field Selection'[Field Value])="Volume",
        CALCULATE(
            DIVIDE(SUM(DBALLSTAT[Profit]),SUM(DBALLSTAT[ChglWt(KG)])),
            FILTER(
                DBALLSTAT,
                DBALLSTAT[Eco_year] = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year])
            ),
            FILTER(
                Group_Logic,
                Group_Logic[Load Type]="HAWB"
            )
        )
    )
return 
    IF(ISFILTERED('DBALLSTAT'[Eco_year]),Select_Year,NotSelect)

SelectYear = 
    SWITCH(
        SELECTEDVALUE('Summary Field Selection'[Field Value]),
        "GP",[MaxYear3_HAWB_Gp/Vol],
        /*"Volume",[MaxYear2_HAWB],*/
        "Yield",[MaxYear3_HAWB_Gp/Vol]
    )

previous year = 
    CALCULATE(
        [SelectYear],
        FILTER(
            ALL('DBALLSTAT'),
            'DBALLSTAT'[Eco_year] = MAX('DBALLSTAT'[Eco_year])-1
            && DBALLSTAT[Month] = MAX(DBALLSTAT[Month])   
        )
    )

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

Graph output not showing Correct.pbix366 KB

sakshikaul
Helper III
5 years ago
Hi
As discussed earlier also, I am getting the result but I want if dballstat(eco_year)= 2021 selecte then the data should be shown for complete 2020 ie ( jan to dec ) and for 2021( jan to feb).
Here you can check in below image only data for 2021 jan n feb, 2020 Jan n feb is being captured. I need for 2020 ( jan to dec hould be captured)

v-kkf-msft

Community 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
Helper III
5 years ago
HI,
It is working fine if I am selecting year as 2021 but its again not working correctly if I am selecting year=2020
Here I am not getting previous year comparison. Previous year comparison is only functinable if I select year =2021

So Now i want if I select year =2021 then the data for 2021 and 2020 should be available
simmilarly if I select year=2020 then the data for 2020 and 2019 should be available on the graph as so on....
sakshikaul
Helper 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

Community Support

5 years ago

Hi sakshikaul ,

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

sakshikaul
Helper 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
Helper 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
Community Support
5 years ago
Hi sakshikaul ,

Yes. My previous reply gave the output you expected.

Please see Message 33 for details.

Best Regards,
Winniz

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 correction

Dax Formula correction help