cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
yfeng
Frequent Visitor

if a date falls under certain time period, return value from another table

Hi Powerbi friends,

 

i have a 2 tables

trascation table (direct query, can't be transform )

consist of 3 columns: date of transcation, service calls, actual CWE 

 

target table ( I created this table myself in powerbi)

I entered a date in each vessel call for the purpose of identifing each Quarter.

please see image

 

https://drive.google.com/open?id=1XcdAbDFiQjUjbMkz5-l60xiqWXqfds98 


how can i create a column/measure in the transcation table that returns a target CWEvalue from the target table.

 

if a transcation date falls between a Quarter in the transcation table, then match the corresponding service call in the target table and give target CWE vaule for that specific Quarter.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: if a date falls under certain time period, return value from another table

Hi,

 

Please try to create two quarter columns in both two tables:

Quarter = QUARTER('Transcation Table'[Date])

Quarter = QUARTER('Table'[Date])

Then create their relationship as many-to-many by [Quarter] link column:

2.jpg

Then try this measure:

Measure = 
CALCULATE (
    MAX ( 'Table'[Target CWE] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Service Calls] IN FILTERS ( 'Transcation Table'[Service Calls] )
            && 'Table'[Quarter] IN FILTERS ( 'Table'[Quarter] )
    )
)

The result shows:

32.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: if a date falls under certain time period, return value from another table

I think you should able to create a Common date table and work with IT. Typically in the mixed-mode, it is allowed. I have not tried Mixed. But refer example of Direct query

 

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Community Support
Community Support

Re: if a date falls under certain time period, return value from another table

Hi,

 

Please try to create two quarter columns in both two tables:

Quarter = QUARTER('Transcation Table'[Date])

Quarter = QUARTER('Table'[Date])

Then create their relationship as many-to-many by [Quarter] link column:

2.jpg

Then try this measure:

Measure = 
CALCULATE (
    MAX ( 'Table'[Target CWE] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Service Calls] IN FILTERS ( 'Transcation Table'[Service Calls] )
            && 'Table'[Quarter] IN FILTERS ( 'Table'[Quarter] )
    )
)

The result shows:

32.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

View solution in original post

yfeng
Frequent Visitor

Re: if a date falls under certain time period, return value from another table

I do have a date table created by my IT department.

the date table also consist of quarters as well

but i'm not sure how the date table will help in this situation.

 

Community Support
Community Support

Re: if a date falls under certain time period, return value from another table

Hi,

 

If you have seperate date table, you can create relationships between them.

And then you can get target data in one table from another table.

Please check my previous reply.

 

Best Regards,

Giotto

Community Support
Community Support

Re: if a date falls under certain time period, return value from another table

Hi,

 

Did my first reply solved your issue?

If so, please mark it as a solution for others to see.

Thanks!

 

Best Regards,

Giotto

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors