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
Reddy5833's avatar
Reddy5833
Icon for Helper II rankHelper II
3 years ago

Hi Greg_Deckler, I have usage data for 5 years, (around 1500 records), the table contains date field, formateed as date data type only, I want to calculate differnce of usage between current qtr and prvs qtr (same as i asked in previos question, but based on proper date field)

 

I'm using below measeu to calculate prev quarter usage,

but it is not giving the result as i expected unless I connect with date table (though I'm not using any field from date table in the measure )

 

 

Prev QTR Usage = CALCULATE(SUM('Sample'[Usage]), DATEADD('Sample'[Date],-1,QUARTER))
This measure is giving blanks, if i dont connect with date table.

 

measure to calculate diff: -- works fine when i connect with date table

Diff =
  CALCULATE(SUM('Sample'[Usage])-CALCULATE(SUM('Sample'[Usage]), DATEADD('Sample'[Date],-1,QUARTER))
  )
 
Measure to get first date as blank -- it is not working even if i connect with date table, giving same result as above
 
Diff 1 =
  IF(SUM('Sample'[YQ Rank])=1, BLANK(),  
  CALCULATE(SUM('Sample'[Water Usage])-CALCULATE(SUM('Sample'[Water Usage]), DATEADD('Sample'[Date],-1,QUARTER)))
  )
 

sample data

 

result when connected with date dimension

 

Result when not connected with date

 

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