cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hfoth Member
Member

Table with multilple date relationships

Hi everyone! I'm pretty new in PowerBI, just tarted a few weeks ago.

 

I'm trying to perfom next:

In one table, I have mutiple columns displaying values. Does values are measures or calculated columns, values that come from different sources (Excel, SQL Server). Since all the data is connected I decided to have it all in one table.

So, my scenario is like this: One table that is displaying values by Country. For this table, I need to have 2 date slicers. The idea is that some columns are impacted with one of the slicer and other columns should be impacted with the other slicer. I guest that maybe this could be done changing simple formulas with others a bit more implicit.

Example:

Me actual YTD SALES whas SUM('Sales'[USD])

How I guest that it could work:

CALCULATE(
    SUM('Sales'[USD]);
    DATESBETWEEN(
     'DateTable1'[Date];
     FIRSTDATE('DateTable1'[Date]);
     LASTDATE('DateTable1'[Date])
    )
   )

 

And an other measure that should be impacted with the other slicer using the other DateTable2.

 

But it is not working for me.

Any suggestions? Can something like this be done?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
kcantor Super Contributor
Super Contributor

Re: Table with multilple date relationships

@hfoth

I am a little confused.  Do you actually have two seperate columns with dates or is it one date column with two other columns of data?

If you have multiple date columns, relate both to your date table. One will have an inactive relation ship. To use the first date, just write you dax as usual. To use the second date with the inactive date, use the USERELATIONSHIP function.

For example, I have multiple dates associated with my fact table. One is order date, the other is posting date (there are others too but keeping this simple).

Sales by order date = SUM('FactTable'[LineSales]) -- this measure uses Order Date as I have it set as the main date.

Sales by Posting date = CALCULATE([Sales by order Date], USERELATIONSHIP('FactTable[PostingDate], 'Date'[DateKey])) -- This uses the same calculation referenced in the measure name but switches to use the inactive relationship.

If this is not what you mean, please clarify the request so that we may offer more help. Also, it never hurts to add a snip of the data model and/or relationships or even some sample data as we are not familiar with your particular data.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
kcantor Super Contributor
Super Contributor

Re: Table with multilple date relationships

@hfoth

I am a little confused.  Do you actually have two seperate columns with dates or is it one date column with two other columns of data?

If you have multiple date columns, relate both to your date table. One will have an inactive relation ship. To use the first date, just write you dax as usual. To use the second date with the inactive date, use the USERELATIONSHIP function.

For example, I have multiple dates associated with my fact table. One is order date, the other is posting date (there are others too but keeping this simple).

Sales by order date = SUM('FactTable'[LineSales]) -- this measure uses Order Date as I have it set as the main date.

Sales by Posting date = CALCULATE([Sales by order Date], USERELATIONSHIP('FactTable[PostingDate], 'Date'[DateKey])) -- This uses the same calculation referenced in the measure name but switches to use the inactive relationship.

If this is not what you mean, please clarify the request so that we may offer more help. Also, it never hurts to add a snip of the data model and/or relationships or even some sample data as we are not familiar with your particular data.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

hfoth Member
Member

Re: Table with multilple date relationships

Hi @kcantor, thanks for your reply.

 

Sorry that maybe my explanation of the case was not clear enough, but anyway your response give me the solution. The USERELATIONSHIP function solve my problem. That, and changing the relationships between tables.

 

Many thanks!

 

Best regards.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 103 members 1,503 guests
Please welcome our newest community members: