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 (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

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.

39 Replies

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

    Hi sakshikaul ,

    I added a condition about the date ie DBALLSTAT[Date] to the formula, you try to see if it can get the result you want. If it is unsuccessful, please provide some sample data and relationships between tables.  

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

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

      Hi,

      Earlier on selecting 2021 filter the data was not getting filtered for 2021. It was showing all the months for 2021 ie jan to dec and same for 2020

      but now I have solved one issue like now on selecting year =2021 I am getting data for jan and feb only which is correct but for 2020 I am not getting values. please help 

      Expression written for current Year

      Maxyear1_hawb=

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

       

      expression for prev year

      PrevYear1_HAWB = var Summary_hawb_prev=MAX(DBALLSTAT[Eco_year])-1 var summary_hawb_prevB= If(SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",CALCULATE(SUM(DBALLSTAT[Profit])/1000,FILTER((DBALLSTAT),DBALLSTAT[Eco_year]=Summary_hawb_prev),FILTER(ALL(Group_Logic),Group_Logic[Load Type] ="HAWB"))) return Summary_hawb_prev

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

        Hi sakshikaul ,

        Modify the measure PrevYear1_HAWB:

        PrevYear1_HAWB = 
var Summary_hawb_prev = MAXX(ALL(DBALLSTAT),DBALLSTAT[Eco_year])-1 
var summary_hawb_prevB = 
  If(
    SELECTEDVALUE('Summary Field Selection'[Field Value])="GP",
    CALCULATE(
      SUM(DBALLSTAT[Profit])/1000,
      FILTER(
        (DBALLSTAT),
        DBALLSTAT[Eco_year] = Summary_hawb_prev
      ),
      FILTER(
        ALL(Group_Logic),
        Group_Logic[Load Type] = "HAWB"
      )
    ) 
  ) 
return Summary_hawb_prev

        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.