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.

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

Hi sakshikaul ，

Try this formula:

 

 

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)

 

 

 

The SelectYear referenced in measure previous year is the previously created measure.

 

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's avatar
sakshikaul
Icon for Helper III rankHelper III
5 years ago

HI,

Its working fine now... but here in Previous year measure (2020) its showing data for only last two months whereas 2020 should show complete data from jan 2020 - dec 2020.. Plesae help