Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jedsh08
Frequent Visitor

Get information based on date range

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

1 ACCEPTED SOLUTION

Thank you it helps .

View solution in original post

9 REPLIES 9
v-zhangti
Community Support
Community Support

Hi, @jedsh08 

 

I simply simulated some data hoping to restore your problem.

vzhangti_0-1638868130190.png

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

vzhangti_1-1638868456229.png

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.

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_0-1639055520298.png

Is it normal? 

yes, that is normal. 

 

Also note the remarks section

LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

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.

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.

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
Super User
Super User

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.

Thank you it helps .

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.