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 This is because you are using CALCULATE. CALCULATE almost requires a star schema to work properly. CALCULATE has a really hard time dealing with single table data models so you get wacky results. See this video:

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

Hi Greg_Deckler , I saw the video, it's brilliant.. Thanks a lot