Forum Discussion

sambou's avatar
sambou
Frequent Visitor
9 years ago
Solved

How to compare two date columns on the same chart

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...
General Comment
Need Help
Tips and Tricks
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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,

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
sambou
Frequent Visitor
8 years ago

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