Forum Discussion

amber_robinson's avatar
amber_robinson
Frequent Visitor
7 years ago
Solved

Sum values for previous half year

Hi,

 

I'm working with data that is reported on a half-yearly basis (Dec to May, and June to Nov). I want to create a measure to show me total consumption last half year, so that I can compare it with consumption this half year. 

 

I have been able to do this for quarterly reporting, using the formula below, but I cannot seem to adapt it to make it work for half yearly reporting. 

 

Consumption_LQ = (CALCULATE(SUM('GG Data'[Consumption]),DATESINPERIOD(Calendar[Date], ENDOFQUARTER('GG Data'[Report end]),-2,QUARTER))-CALCULATE(SUM('GG Data'[Consumption]),DATESINPERIOD(Calendar[Date],ENDOFQUARTER('GG Data'[Report end]),-1,QUARTER)))

 

Where:

 GG data is the main data table

 Calendar is the reference calendar table

 Report End is the date column in the main data table, and will always read the last date in the reporting period

 

Any suggestions much appreciated.

 

Thanks,

Amber

Need Help
  • tex628's avatar
    tex628
    7 years ago
    Consumption_LY = 
VAR tyear = YEAR(TODAY())
VAR tmonth = MONTH(TODAY())
Return
IF( tmonth <12 && tmonth >5 ,
CALCULATE(SUM('GG Data'[Consumption]) ,
Calendar[Date] >= DATE(tyear-1 , 12 , 1) , 
Calendar[Date] < DATE(tyear , 6 , 1) 
) ,
CALCULATE(SUM('GG Data'[Consumption]) ,
Calendar[Date] >= DATE(tyear-1 , 6 , 1) , 
Calendar[Date] < DATE(tyear , 12 , 1) 
)

2 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago
    Consumption_LY = 
VAR tyear = YEAR(TODAY())
VAR tmonth = MONTH(TODAY())
Return
IF( tmonth <12 && tmonth >5 ,
CALCULATE(SUM('GG Data'[Consumption]) ,
Calendar[Date] >= DATE(tyear-1 , 12 , 1) , 
Calendar[Date] < DATE(tyear , 6 , 1) 
) ,
CALCULATE(SUM('GG Data'[Consumption]) ,
Calendar[Date] >= DATE(tyear-1 , 6 , 1) , 
Calendar[Date] < DATE(tyear , 12 , 1) 
)
    • amber_robinson's avatar
      amber_robinson
      Frequent Visitor
      7 years ago

      Thank you - this was really helpful!

       

      I ended up modifying your suggestion slightly to get it to work, as follows:

       

      Consumption_LHY2 = VAR tyear = YEAR(LASTDATE('GG data'[Report end])) VAR tmonth = MONTH(LASTDATE('GG data'[Report end])) Return IF( tmonth <12 && tmonth >5 , CALCULATE(SUM('GG Data'[Consumption]) , Calendar[Date] >= DATE(tyear-1 , 12 , 1) , Calendar[Date] < DATE(tyear , 6 , 1) ) , CALCULATE(SUM('GG Data'[Consumption]) , Calendar[Date] >= DATE(tyear-1 , 6 , 1) , Calendar[Date] < DATE(tyear-1 , 12 , 1) ) )