Forum Discussion

Ibrahim_shaik's avatar
Ibrahim_shaik
Helper V
1 year ago
Solved

Dynamic Table Visual - Same date last Year

Hi Power BI Community,   I have a table in which i have date, time, temperature, humdity and power column is in another table and I want to create a table visual where the user selects the date fro...
"Need Help"
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi Ibrahim_shaik 

    Have a last year column in the Main Table using the Date column that you have:
    LY Date= DATEADD('MainTable'[Date], -1, YEAR)

    We can achieve the previous year value using LOOKUP:

    Temperature_LY =
LOOKUPVALUE(
    'MainTable'[Temperature],
    'MainTable'[Date], 'MainTable'[LastYearDate],
    'MainTable'[Time], 'MainTable'[Time]
)

    Do the same for Humidity and since Power is from another table:

    Power_LY =
LOOKUPVALUE(
    'PowerTable'[Power],
    'PowerTable'[Date], 'MainTable'[LastYearDate],
    'PowerTable'[Time], 'MainTable'[Time]
)



MohamedFowzan1's avatar
MohamedFowzan1
Super User
1 year ago

Hi Ibrahim_shaik 

This could be used to get the last year: LastYearDate = DATEADD('Date'[Date], -1, YEAR)

and if you actually want to calculate the measures for last year as well. Something like this would suffice:
Temperature LY =
VAR LDate = DATEADD('Date'[Date], -1, YEAR)
RETURN
CALCULATE(
AVERAGE('MainTable'[Temperature]),
FILTER(
ALL('MainTable'),
'MainTable'[Date] = LDate &&
'MainTable'[Time] = SELECTEDVALUE('MainTable'[Time])
)
)

Please provide more context if this is not what you are looking for.



Ibrahim_shaik's avatar
Ibrahim_shaik
Helper V
1 year ago

Hi MohamedFowzan1 ,

 

Thank you for providing the solution but I have tried this DAX measure this gives the total Average of temperature but I don't want any aggregation I only want to show the column as it is in the table visual.

when the user selects a date for example today's date the table visual should show same date last year date column, time column(hourly), temperature(hourly temperature values).

 

I hope i have the provided the context clearly.

 

Thank you.

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Super User
    1 year ago

    Hi Ibrahim_shaik 

    Have a last year column in the Main Table using the Date column that you have:
    LY Date= DATEADD('MainTable'[Date], -1, YEAR)

    We can achieve the previous year value using LOOKUP:

    Temperature_LY =
LOOKUPVALUE(
    'MainTable'[Temperature],
    'MainTable'[Date], 'MainTable'[LastYearDate],
    'MainTable'[Time], 'MainTable'[Time]
)

    Do the same for Humidity and since Power is from another table:

    Power_LY =
LOOKUPVALUE(
    'PowerTable'[Power],
    'PowerTable'[Date], 'MainTable'[LastYearDate],
    'PowerTable'[Time], 'MainTable'[Time]
)