Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou'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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHmmm, definitely seems like a valid solution... but if I try it, it returns the following error message:
@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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYeah, 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.
If you place this measure in one visual everything is ok?
Is it possible to share a sample of PBIX file?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |