How to compare two date columns on the same chart

[sambou]

Hi There,

is there a way to create a chart/table in Power BI similar to the table below? to compare the revenue from two different weeks on the same table? I’m trying to pull data from SQL Server and compare the revenue of prior week (column 8/25/2017) to current week revenue (column 8/31/2017) then create a calculated measure as the last column (Difference). I can’t figure out how to have two date columns on a Power BI table or chart similar to columns below.

Region	8/25/2017	8/31/207	Difference
North American entities	15,000,000	16,700,000	1,700,000
Canadian entities	19,475,000	23,000,000	3,525,000
Latin American entities	8,000,000	5,900,000	(2,100,000)
Japanese entities	9,100,000	11,000,000	1,900,000
Australian entities	5,000,000	4,700,000	(300,000)
United Kingdom entities	13,000,000	11,400,000	(1,600,000)
Rest of Europe entities	17,000,000	18,000,000	1,000,000
Company Total	86,575,000	90,700,000	4,125,000

thanks,

Sam

[v-sihou-msft · Accepted solution]

@sambou

We can create a measure to get the difference of two selected dates.

Difference =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALLSELECTED ( Table1[Date] ), Table1[Date] = MAX ( Table1[Date] ) )
)
    - CALCULATE (
        SUM ( Table1[Value] ),
        FILTER ( ALLSELECTED ( Table1[Date] ), Table1[Date] = MIN ( Table1[Date] ) )
    )

However, We can't have this measure appeared on Grand Total level only. It will always appear under each column group. 

So I suggest you create another table visual, and bring Region field and Difference measure only. 

Regards,

[sambou]

Hi Simon,

the measure did not work, It didn't create two column dates. How did you add the spelled out date on the top of column header? did you specify dates in the mesure? from the Revenue table I selected the highlighted columns below.

Difference =

CALCULATE (

   SUM ( Revenue[Revenue in USD] ),

   FILTER ( ALLSELECTED ( Revenue[As of Date] ), Revenue[As of Date] = MAX ( Revenue[As of Date] ) ))

   - CALCULATE (

       SUM ( Revenue[Revenue in USD] ),

       FILTER ( ALLSELECTED ( Revenue[As of Date] ), Revenue[As of Date] = MIN ( Revenue[As of Date] ) )  )

Thanks,

Sam