Retrieve value same date current year

[Anonymous]

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!

WeightedDegreeDays Same Date Current Year column = 

IF(YEAR(GasUsagePerDegreeDay[Date]) = YEAR(NOW()), GasUsagePerDegreeDay[WeightedDegreeDays], GasUsagePerDegreeDay[WeightedDegreeDays] YEAR(NOW())

[MFelix · Accepted solution]

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

[Anonymous]

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!

[MFelix]

@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

[Anonymous]

Hmmm, definitely seems like a valid solution... but if I try it, it returns the following error message:

[MFelix]

@Anonymous,

As I can see from the error the problem comes from your first measure CorrectGasUsage what kinfd of information are you calculating in this measure.

MFelix

[Anonymous]

Yeah, it's the measure we started with:

WeightedDegreeDays SDCY = 
VAR date_select =
    MIN ( GasUsagePerDegreeDay[Date] )
RETURN
    CALCULATE (
        SUM ( GasUsagePerDegreeDay[WeightedDegreeDays] ),
        GasUsagePerDegreeDay[Date]
            = DATE ( YEAR ( NOW () ), MONTH ( date_select ), DAY ( date_select ) )
    )

So it returns the DegreeDays (an energy-technical term relating weather and gas usage) of the same day and month, but for the current year.

[MFelix]

If you place this measure in one visual everything is ok?

Is it possible to share a sample of PBIX file?

Regards,

MFelix