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 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.

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



4 Replies

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



  • 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