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]
)



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]
)