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



samratpbi's avatar
samratpbi
Super User
1 year ago

Hi, first of all, for your date and time, you need to create 2 columns ( 1 column to contain date id, another to contain hour id). Then have a join with date dimension using date key.
Then in your table or matrix visual, you can add hours ( you may create an hour dimension table also and make a join with the fact table) and have a calculation created like below:

CALCULATE(SUM(column_name),sameperiodlastyear('dim_date'[Date]))

 

Hope this helps to resolve your problem. If it does, then please mark it as solution, thanks - Samrat