Table with multilple date relationships

[Anonymous]

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.

[kcantor · Accepted solution]

@Anonymous

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.

[Anonymous]

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.