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

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

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

       

      Is it possible to share a sample of PBIX file?

       

      Regards,

      MFelix