cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 405 members 3,742 guests
Please welcome our newest community members: