Forum Discussion

Reddy5833's avatar
Reddy5833
Icon for Helper II rankHelper II
3 years ago
Solved

How to calculate difference between current quarter and previous quarter

Hi, can somebody help me with this. Available data Year-QTR            Quarter rank     usage 2021-1 1 1021.4 2021-2 2 964 2021-3 3 1023.7 2021-4 4 1042.6 2022-1 5 104...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Reddy5833 Try:

    Measure =
  VAR __Current = SUM('Table'[usage])
  VAR __CurrentQR = MAX('Table'[Quarter rank])
  VAR __Previous = SUMX(FILTER(ALL('Table'),[Quarter rank] = __CurrentQR - 1),[usage])
  VAR __Result = IF(__Previous = BLANK(),BLANK(),__Current - __Previous)
RETURN
  __Result
  • Reddy5833's avatar
    Reddy5833
    3 years ago
     
    I got the answer to my question, I found it from your video ( MSHGQM - Power BI DAX - Don't Use CALCULATE!) 
     
    Measure to be used when there are multiple categories.

    Measure 1 =
    VAR __Site = VALUES('Sample'[Site Name])
     VAR __Current = sum('Sample'[Usage])
    VAR __Table = FILTER(ALLSELECTED('Sample'),'Sample'[Site Name] in  __Site) 
      VAR __CurrentQR = MAX('Sample'[YQRank])
      VAR __Previous = SUMX(FILTER(__Table,[YQRank] = __CurrentQR - 1), [Usage])
      VAR __Result = IF(__Current=BLANK(),"0",__Current - __Previous)
    RETURN
    __Result
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

Reddy5833 Try:

Measure =
  VAR __Current = SUM('Table'[usage])
  VAR __CurrentQR = MAX('Table'[Quarter rank])
  VAR __Previous = SUMX(FILTER(ALL('Table'),[Quarter rank] = __CurrentQR - 1),[usage])
  VAR __Result = IF(__Previous = BLANK(),BLANK(),__Current - __Previous)
RETURN
  __Result
  • Reddy5833's avatar
    Reddy5833
    Icon for Helper II rankHelper II
    3 years ago

    Greg_Deckler Thanks a lot, it works perfect..

     

    • Reddy5833's avatar
      Reddy5833
      Icon for Helper II rankHelper II
      3 years ago
       
      I got the answer to my question, I found it from your video ( MSHGQM - Power BI DAX - Don't Use CALCULATE!) 
       
      Measure to be used when there are multiple categories.

      Measure 1 =
      VAR __Site = VALUES('Sample'[Site Name])
       VAR __Current = sum('Sample'[Usage])
      VAR __Table = FILTER(ALLSELECTED('Sample'),'Sample'[Site Name] in  __Site) 
        VAR __CurrentQR = MAX('Sample'[YQRank])
        VAR __Previous = SUMX(FILTER(__Table,[YQRank] = __CurrentQR - 1), [Usage])
        VAR __Result = IF(__Current=BLANK(),"0",__Current - __Previous)
      RETURN
      __Result
  • Reddy5833's avatar
    Reddy5833
    Icon for Helper II rankHelper II
    3 years ago

    Hi Greg

    Hope you are doing good

     

    The Measure you share is working fine when I have only one category, but when I have multipel categories in my dataset it is unable to find the difference between current quarter and previous quarter when I choose a category from the slicer.

    But it works fine when we select all categories from slicer it is showing the over all differnce between current quarter and prev quarter.


    I'm unable to use calculate function in the measure it is giving wrong values if I do so

     

    Below is the measre you shared a while ago.

    Measure =
  VAR __Current = SUM('Table'[usage])
  VAR __CurrentQR = MAX('Table'[Quarter rank])
  VAR __Previous = SUMX(FILTER(ALL('Table'),[Quarter rank] = __CurrentQR - 1),[usage])
  VAR __Result = IF(__Previous = BLANK(),BLANK(),__Current - __Previous)
RETURN
  __Result