Dynamic Table Visual - Same date last Year

[Ibrahim_shaik]

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 from date slicer and the table visual shows same date last year date, time, temperature, humidity and power. I have a table visual which shows current selected date and other parameters but I want to see the same date last year as well. I have houlry data per day and it should be same in the table visual as well.

Please let me know how i can achieve this.

Thank you.

Regards,

Ibrahim.

[samratpbi]

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

[MohamedFowzan1]

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]

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 · Accepted solution]

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