Solved: Re: Graph output not showing Correct - Page 2

sakshikaul · ‎03-21-2021

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 (2020 all the months ie Jan -Dec should be shown in graph) on selection of 2021(current Year)

But in my following graph data for 2021 is showing me all the months( Jan -Dec( whereas it should only show jan , feb) and 2020 also data is captured for all the months . Please help

Year selected=2021

Following is the DAX written in Power BI

MaxYear1_HAWB = If(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",CALCULATE(SUM(DBALLSTAT[Profit])/1000,FILTER((DBALLSTAT),DBALLSTAT[Eco_year]=MAX(DBALLSTAT[Eco_year])),FILTER(Group_Logic,Group_Logic[Load Type]="HAWB")))

whereas I am basically migrating qlikview application to power BI so in qlikview the expression is written as

=if(substringcount(Concat(distinct '~' & [Load Type] & '~' ,'|'),'~HAWB')=1,

SUM({<Eco_year = {'$(Maxyear)'},[Load Type]={'HAWB'}>}(Profit)/1000)

,'')

Thanks in advance

v-kkf-msft · ‎03-26-2021

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.

View solution in original post

sakshikaul · ‎03-24-2021

For other visuals I am using dballlstat(eco_year). This is the reason I cannot have two seperate calenders in one report.

Can this be replaced by using previousYear function?

and also , for yeild I have a following expression

If(SELECTEDVALUE('Summary Field Selection'[Field Value])="Yield", 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))

I am trying to write above expression in the dax provided by you I am getting an error. Please help

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)

v-kkf-msft · ‎03-25-2021

Hi @sakshikaul ,

You come up with a great idea, I test it and it worked.
Based on the previous discussion, create the following measure:

SelectYear = 
    SWITCH(
        SELECTEDVALUE('Summary Field Selection'[Field Value]),
        "GP",[MaxYear1_HAWB],
        "Volume",[MaxYear2_HAWB],
        "Yield",[MaxYear3_HAWB]
    )

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

Using this method, you don't need to calculate the previous year's value separately for different field values.

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 · ‎03-25-2021

Here in this expression select_year is giving me an error

sakshikaul · ‎03-25-2021

Following expression is giving me an error when I am trying to apply formula for yeild. Please Chelp in resolving the issue and MEANWHILE i WILL TRY CREATING PREVIOUS YEAR DAX AS YOU SUGGESTED-

MaxYear3_HAWB_Gp/Vol =
var MaxYear = MAXX(ALL('DBALLSTAT'),'DBALLSTAT'[Eco_year])
var SelectYear =
If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP/Volume",
DIVIDE(CALCULATE(SUM(DBALLSTAT[Profit]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Eco_year]),ALLSELECTED(Slicer[Eco_year])-1}
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB")),
CALCULATE(SUM(DBALLSTAT[ChglWt(KG)]),
FILTER(
DBALLSTAT,
DBALLSTAT[Eco_year] in {ALLSELECTED(Slicer[Eco_year]),ALLSELECTED(Slicer[Eco_year])-1}
),
FILTER(
Group_Logic,
Group_Logic[Load Type]="HAWB"),0
)
)
)
var NotSelect =
If(
SELECTEDVALUE('Summary Field Selection'[Field Value])="GP/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(Slicer),SelectYear,NotSelect)

fOLLOWING IS THE iMAGE ATTACHED

v-kkf-msft · ‎03-25-2021

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 · ‎03-25-2021

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 · ‎03-25-2021

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

sakshikaul · ‎03-25-2021

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 · ‎03-25-2021

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 · ‎03-26-2021

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 · ‎03-26-2021

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 · ‎03-30-2021

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 · ‎03-30-2021

Hi @sakshikaul ,

Yes. My previous reply gave the output you expected.

Please see Message 33 for details.

Best Regards,
Winniz

sakshikaul · ‎03-30-2021

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 · ‎03-26-2021

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 · ‎03-25-2021

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

sakshikaul · ‎03-24-2021

Firstly If I am not selecting any year filter [ie dballstat(year)] then I am getting correct value as shown in mage

But when I am selecting dbalstat(eco_year)= 2021 the I am getting values for that year only as shown in below :-

Third thing is that if I am taking only one measure , on axis-month, legend as eco_year then, selecting - volume with no eco year filter(no year is selected) then I am getting correct value

as shown below:-

but if I am adding more than one measure in a chart it is getting automatically added in a tooltip and then selecting GP, on axis- month, legend- year, I am not getting value for GP

shown below

please help

v-kkf-msft · ‎03-25-2021

Hi @sakshikaul ,

If your report does not contain sensitive data, can you share your report? It will help us solve the problem faster.

Best Regards,
Winniz

sakshikaul · ‎03-25-2021

No, I cant share my application as its containing comapny's data.

I am very close to the solution. I just want now 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

Graph output not showing Correct

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly