Forum Discussion
Retrieve value same date current year
From below table, for years previous to the current year, I want to retrieve the value of WeightedDegreeDays for the same day + month but of the current year. So for the first row in this example (6 March 2016) I want to retrieve the value for WeightedDegreeDays for 6 March 2017.
DegreeDays CurrentYear
My first thought is to make a calculated column with a formula that looks something like this (in which the red part is not correct at the moment). If the solution is in a measure instead of a calculated column, I am also interested in that. Thank you!
Hi Anonymous,
You can use a column or a measure, taking into account that the column value will ocupy space in your model and the best practices for the DAX usage is to make a measure if possible and don't use calculated columns below you have the formula for the measure:
Value_Date = VAR date_select = MIN ( GasUsagePerDegreeDay[Date] ) RETURN CALCULATE ( SUM ( GasUsagePerDegreeDay[WeightedDegreeDays] ), Table1[Date] = DATE ( YEAR ( NOW () ), MONTH ( date_select ), DAY ( date_select ) ) )However if you want to do it in a column the formula should be this one:
Value_Date_ = VAR date_lookup = Table1[Date] RETURN LOOKUPVALUE ( GasUsagePerDegreeDay[WeightedDegreeDays], GasUsagePerDegreeDay[Date], DATE ( YEAR ( NOW () ), MONTH ( date_lookup ), DAY ( date_lookup ) ) )Regards,
MFelix
7 Replies
Hi Anonymous,
You can use a column or a measure, taking into account that the column value will ocupy space in your model and the best practices for the DAX usage is to make a measure if possible and don't use calculated columns below you have the formula for the measure:
Value_Date = VAR date_select = MIN ( GasUsagePerDegreeDay[Date] ) RETURN CALCULATE ( SUM ( GasUsagePerDegreeDay[WeightedDegreeDays] ), Table1[Date] = DATE ( YEAR ( NOW () ), MONTH ( date_select ), DAY ( date_select ) ) )However if you want to do it in a column the formula should be this one:
Value_Date_ = VAR date_lookup = Table1[Date] RETURN LOOKUPVALUE ( GasUsagePerDegreeDay[WeightedDegreeDays], GasUsagePerDegreeDay[Date], DATE ( YEAR ( NOW () ), MONTH ( date_lookup ), DAY ( date_lookup ) ) )Regards,
MFelix
You're a wizard MFelix! I chose the measure in the end (and named it 'WeightedDegreeDays SDCY') as the column didn't work anyway because I'm using DirectQuery and the LOOKUPVALUE function is not allowed in DirectQuery. I can now reuse the measure 'WeightedDegreeDays SDCY' in this measure:
CorrectedGasUsage = SUM(GasUsagePerDegreeDay[GasUsagePerDegreeDay]) * GasUsagePerDegreeDay[WeightedDegreeDays SDCY]
Now the next question comes up, because I want to use the measure 'CorrectedGasUsage' in the next measure so I can switch it on/off with a filter:
GasUsage measure = IF( CONTAINS(xCorrectedGasUsageOnOff, xCorrectedGasUsageOnOff[Corrected Gas Usage On/Off], "On"), CALCULATE( SUM(GasUsagePerDegreeDay[CorrectedGasUsage]) ), IF( CONTAINS(xCorrectedGasUsageOnOff, xCorrectedGasUsageOnOff[Corrected Gas Usage On/Off], "Off"), SUM(GasTelemetryData[GasUsageCubicMeters]) ))
But here the CorrectedGasUsage measure isn't accepted. Any thoughts? Thanks!
Anonymous,
The question is that you are trying to do a SUM of a measure so you are so it a litle bit redundant, also regarding the IF I personnaly prefer the SWITCH formula is much more flexible and practical since you can do concatenated IF in a row.
GasUsage measure = SWITCH ( TRUE (), CONTAINS ( xCorrectedGasUsageOnOff, xCorrectedGasUsageOnOff[Corrected Gas Usage On/Off], "On" ), [CorrectedGasUsage], CONTAINS ( xCorrectedGasUsageOnOff, xCorrectedGasUsageOnOff[Corrected Gas Usage On/Off], "Off" ), SUM ( GasTelemetryData[GasUsageCubicMeters] ) )Try this.
Regards,
MFelix