Forum Discussion

thanish's avatar
thanish
Frequent Visitor
9 years ago

Two dates from the same date column

Hi all, I have requirement to compare the sales(for example) for two different periods seperately in two different charts.  Consider the below table as a sample where there is only one date field  ...
Need Help
OwenAuger's avatar
OwenAuger
Super User
9 years ago

Hi thanish

 

One approach would be to have two calendar tables, one with an active relationship to your Sales table and one with an inactive relationship.

 

Sample model here

 

Data model would look like this:

Then define measures something like this:

Sales Amount = SUM ( Sales[Sales] )

Sales Amount Compare = 
CALCULATE (
    [Sales Amount],
    ALL ( 'Calendar Select' ),
    USERELATIONSHIP ( Sales[Date], 'Calendar Compare'[Compare Date] )
)

I'm assuming you might want to use both measures in the same expression (to calculate the difference for example) so you would need both to evaluate within the same filter context.

 

I'm not 100% sure that would meet all your needs but hopefully is of some use.

 

Owen :)