Get information based on date range

[jedsh08]

Hello , 

I am really new to the world of data and also with power bi. I am trying to do a comparison between information on date ranges. 

I am pulling out 07 days information (Friday to Thursday) with a date filter and I want information on the 07 days which precede the first selection.
I explain more, what I want is to compare the week S ( Friday to Thursday) to S-1 (previous friday to Thursday).
And then I can make my comparison.

Thank

[v-zhangti]

Hi, @jedsh08 

I simply simulated some data hoping to restore your problem.

Date2 = DATEADD('Table'[Date],-7,DAY)

Value2 =
LOOKUPVALUE ( 'Table'[Value], 'Table'[Date], [Date2] )

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

[jedsh08]

After trying what you have said, I understand that it will work, but I have another problem when applying your solution. I am connected in DirectQuery mode and when trying calculate columns, I cannot find the LOOKUPVALUE() function. 

Can you tell me what's wrong? 

Thanks for your help.

[jedsh08]

Is it normal? 

[lbendlin]

yes, that is normal. 

Also note the remarks section

LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

[jedsh08]

Will I use the Related() function in this case? I also want to mention that my result column (based on the 7 selected days) is a measure Dax and I indicated that I cannot include a measure in another measure.

[lbendlin]

RELATED() is only something you need for calculated columns. Measures use the data model.  If your tables are unrelated then you can try TREATAS() but that may have the same Direct Query limitations.

Please provide more details about your actual setup, the participating data sources, the data model etc.  It is difficult to help if the overall scenario is not clear.

[jedsh08]

My actual setup is :

All of my tables use Direct Query mode. So to calculate something, I always have to do Measure. I'm not able to use a calculated column.

Let me explain your clarely what I have and what I want.

I have a table named DIM_Temps which is a date table and all other tables are values tables. 

With that, I have a Folder with all measures of the project, and the one that interests me is named Ponctu_O.

This measure is already calcultate using the Dax expression DIVIDE so there is no problem at this point. 

The fact is that in a visual table, I'm able to show [Ponctu_O]  and filter the value with a segment visual which contains the date. 

And now, I want to be able to show the value of this measure for the 7 days before the date selected in the date filter.

I'm not able to create a new column with the second date because I'm in Direct Query Mode.

Do you understand my situation better?

[lbendlin]

There is a DAX function DATEADD  that you can use for that. It is recommended that you use a proper calendar table in your data model.

[jedsh08 · Accepted solution]

Thank you it helps .