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

 

MonthSales
Jan1000
Feb1500
Mar4100
Apr466
May4100
Jun5615
Jul5145
Aug7800
Sep4830
Oct5478
Nov4871
Dec6874

 

 

My client has a requirement to compare the sales from Jan to June with July to December in two seperate graphs even though we can use a single bar graph for the entire year data. So he wants to have two date pickers first one is "Select date" and the second date picker is "Compare to date"

 

So in the "Select date" I will have to select Jan to June and in the "Compare to date" I will have to select July to December or any date of choice. The problem here is both the date picker will be fetching from the same date column.

As PowerBI charts are interactive if I select Jan to June in the "Select date" it is going to mask July to December in the "Compare to date" and ultimately will display charts only for Jan to June. Kindly help me how this situation can be overcomed

 

Need Help

6 Replies

  • 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 :)

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    You can stop the interactions between the slicers & charts by changing the settings in the edit interactions option.

    See the attached screenshot.

     

     

     

    and in the other slicer, You can deselect the no of months not required in the page level filters, only keeping the months for which you required to show the value. 

     

    • thanish's avatar
      thanish
      Frequent Visitor
      9 years ago

      Hi BhaveshPatel the page level filter is going to be constant throughout. But Jan to June and July to December is just a sample I gave. It need not always be the same. Sometimes they might want to compare between Jan to March with July to Sept or Jan to March with Oct to December or between any two period for that matter like July to  Sept 2015 with July to  Sept 2016 . So I don't think Page level filter will be a good idea. Your thoughts ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi thanish,

        From my perspective of view, as you want to compare sales between any periods, it is better to have another two calendar tables and create relationships among the three tables( calendar tables and sales table) as OwenAuger's post. That way, it is more convenient to select different months in the two slicers and compare sales.

         

        Thanks,
        Lydia Zhang