I need to create a calculated table on the fly when the user selects a value from the slicer/filter

[Hussain]

Hi,

I am able to create measures using the selectedvalue dax measures for the calculations I want. But I also want that related content to be displayed in a tabular form.
My use-case/example: I am calculating 7 day change %. I am able to do the calculation but Power BI does not have the feature to show the related data/content in tabular form for the week selected and the previous week. 

Also, I want it to default to current 7 days and last 7 days and when the user selects a date, he/she should be able to view the data for the last 14 days ending at the date selected.

(It is possible for me to calculate the measures 7 day change % but displaying the date with all the above conditions is a challenge)

There should also be a legend to compare the latest 7 days over the previous 7 days data.

Any help is appreciated.

Thanks!

[v-angzheng-msft · Accepted solution]

Hi, @Hussain 

Step 1: Create a calculated table that summarizes the date fields

Date = SUMMARIZE('Fc_table',[Date])

Step 2: Create the three measures below and filter the measures in the filter pane

SelectedValue = 
SELECTEDVALUE('Date'[Date])

Last 14 Days = 
var _lastN=14
return 
IF (
    MAX ( 'Fc_table'[Date] )
        IN GENERATESERIES ( [SelectedValue] - _lastN+1, [SelectedValue], 1 ),
    1,
    0
)

Last or Previous = 
var _preN=7
var _lastN=7
var _currentdate=MAX ( 'Fc_table'[Date] )
var _date1=[SelectedValue] - (_lastN+_preN)+1
var _date2=[SelectedValue]-_lastN

var _if=
SWITCH(TRUE(),
    _currentdate IN GENERATESERIES ( _date1, _date2, 1 ),"Previous 7 Days",
    _currentdate IN GENERATESERIES ( _date2+1, [SelectedValue], 1 ),"Last 7 Days"
)
return _if

Step 3: Create a Slicer with the Date Field in the Summary Table

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Hussain , if you select one date and want more than that, then you need an independent date table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Hussain]

Hi Amit, 

Thanks for your reply.

I am specifically looking to show the table only the date selected and 14 days beyond that. 

In your attached video, you've shown how to do measures. 

But is it possible to filter data and show it in tables and maps(I have coordinates in the table) with the abovementioned parameters with the legends called "Latest 7 days" and "Previous 7 days"?