Comparing and Calculating Sales for Two Independent Date Periods with Percentage Difference

[Braytron5]

Hello everyone,

I am working on a Power BI report where I want to compare sales (Net total) for two different time periods and then calculated difference show in percentage. I created two table visuals with two date slicers. Each slicer is interacting with one table. Total from those tables, I want to calculate and show in percentage. Problem is that slicers in new table visual do not show different value and calculation shows 0.

[danextian · Accepted solution]

Hi @Braytron5 

That isn't how crossfiltering works. While you can make a viz crossfilter  only a  specific viz, you cannot make it partially do so such that only a column or a measure is affected - it either crossfilters or it doesnt at all. In order to make a two-period comparison, you can't be using a range slicer as there aren't two ranges in there - only one - from the start to end date. A way to achieve this is by using a disconnected table for one of the measures. A disconnected date table can be created by referencing the related date table in a calculated table or by creating one in the query editor without relating it to the other fact/dim tables.

In the sceenshot  below, Period1 is just a simple as this will be filtered by the related date table.

Sales = 
SUM ( Sales[Sales Amount] )

 Period2 is as follows

Sales Period2 = 
CALCULATE (
    [Sales], 
    // Map the selected dates from the DisconnectedDate table to the 'Date' table, maintaining any existing filters on DisconnectedDate[Date].
    KEEPFILTERS ( TREATAS ( VALUES ( DisconnectedDate[Date] ), 'Date'[Date] ) ), 
    // Remove any filters applied directly to the 'Date' table to ensure TREATAS controls the filtering.
    REMOVEFILTERS ( 'Date' )
)

Please see sample pbix for the details.

[DataNinja777]

Hi @Braytron5 ,

To compare sales (Net Total) for two different time periods in Power BI using separate date slicers, the issue likely stems from slicers not being isolated properly. To resolve this, you need to use two independent date tables. Start by duplicating your existing Date Table and renaming the duplicates to Date1 and Date2. These tables should not have relationships with your fact table to ensure they function independently.

Next, create measures to calculate the Net Total for each period. For the first period, the measure would be:

Net Total Period 1 = 
CALCULATE(
    SUM(FactTable[Net Total]),
    TREATAS(VALUES(Date1[Date]), FactTable[Date])
)

For the second period, create a similar measure:

Net Total Period 2 = 
CALCULATE(
    SUM(FactTable[Net Total]),
    TREATAS(VALUES(Date2[Date]), FactTable[Date])
)

To calculate the percentage difference between the two periods, use a measure like this:

Percentage Difference = 
IF(
    [Net Total Period 1] = 0, 
    BLANK(), 
    ([Net Total Period 2] - [Net Total Period 1]) / [Net Total Period 1]
)

You can display these measures in a card or KPI visual. Ensure that the slicers for Date1 and Date2 interact only with their respective visuals by editing the interactions in Power BI. This setup ensures that the totals from the two different time periods are calculated independently and that the percentage difference is displayed correctly. If the slicers do not function as expected or show overlapping values, double-check that Date1 and Date2 are not related to the main Date table in your model.

Best regards,

[edlng]

Hi @DataNinja777, 
I had the same problem and your answer really helped me. I just have an issue now to visualize data using only period 1, because there is no relationships between the fields. Do I have to create another measure to resolve that?
Thank you!

[Bibiano_Geraldo]

Hi @Braytron5 ,

Its not clear for me, sorry.

I mean, the values are equal thats why is returning zero, because you selected the same date range in both slicer's.

Can you explain me more, please?