Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    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

     

MFelix's avatar
MFelix
Super User
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    MFelix
    Super User
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

       

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        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