Snapshot Comparison based on dynamic date selection

[sandy_PBI]

Hello,

I am trying to build a logic to build a table which compares 5 meaures for user selected snapshot data.

currently i am only provided a table which compares the latest snapshot and previous snapshot and i am doing this comparison in SQL and uploading those measures on Power BI.

The requirement is provide a table which shows the comparison of 2 selected dates.

The current visual a single select filter to provide data for a single snapshot. (comparison table pasted below). Prior and current measures are calculated in SQL.

In order to provide comparison, I am thinking of the following : 

Create 2 tables (table1 and table2) for dates with single select and then calculate the comparative measures, %age change, etc between the correspinding measures and display in a table. 

Help needed : 

1. is there a better approach to handle this.

2. if i create 2 tables, how do i compare the measures for the selected date? 

appreicate any inputs.

thanks,

[AnalyticsWizard]

@sandy_PBI 

To compare two user-selected dates in Power BI using DAX, you can indeed create a more dynamic solution without the need for two separate tables. Here’s a high-level approach:

1. Create a Date Parameter Table: This table will allow users to select two different dates for comparison. You can create this table manually or use DAX to generate it.

2. Create Measures for Each Date Selection: Use DAX to create measures that calculate the values for each of the selected dates. You’ll need to use the CALCULATE function along with the FILTER function to apply the selected dates to your calculations.

3. Calculate the Differences and Percentage Changes: Create additional measures that calculate the differences and percentage changes between the two selected dates.

4. Display in a Table Visual: Use a table visual to display your original measures along with the new comparative measures. You can use the date parameter table to control the date selection.

Here’s a simplified example of how you might set up one of these measures:

MeasureForSelectedDate1 = 
CALCULATE(
    [YourOriginalMeasure],
    FILTER(
        ALL('DateTable'),
        'DateTable'[Date] = SELECTEDVALUE('DateParameterTable'[Date1])
    )
)

And for the percentage change:

PercentageChange = 
DIVIDE(
    [MeasureForSelectedDate2] - [MeasureForSelectedDate1],
    [MeasureForSelectedDate1]
)

This approach allows for a flexible comparison of any two dates selected by the user and can be adapted to include as many measures as needed. Remember to replace [YourOriginalMeasure], 'DateTable', and 'DateParameterTable' with the actual names of your measures and tables. The SELECTEDVALUE function is used to retrieve the user’s selection from the parameter table.

[sandy_PBI]

thanks AnalyticsWIzard for a details explanation. 

I followed your steps but am getting a boolean output.

MRP_WATERFALL[TOTAL_DEMAND_M] is the expressesion 

LAGWEEK is the snapshot table.

TOTAL_DEMAND_SELECTED = CALCULATE(

                             MRP_WATERFALL[TOTAL_DEMAND_M],

                                                         FILTER(ALL(MRP_WATERFALL[LAGWEEK]),MRP_WATERFALL[LAGWEEK])) = SELECTEDVALUE(MRP_WATERFALL[LAGWEEK])

I am trying to leverage the same table LAGWEEK which is currently used a filter instead of creating a new table. 

Is that the reason why my output is a boolean? 

Secondly is there a way that i can leverage the same table LAGWEEK instead of creating of creating another one?

Best Rgds. 

[sandy_PBI · Accepted solution]

just an update how i solved this : 

i created 2 tables for dates in SQL DB, uploaded them as independent tables and linked to the main table (which contained the meaures) using SELECTEDVALUE.

thanks all for inputs.

[sandy_PBI]

can you please help to explain a little more in detail? 

thanks,

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[sandy_PBI]

thanks, 

The data table looks like below (table 1) where I have forecasted/actual sales captured everyweek for the same week. 

I have created a filter for snapshot week single select filter for snapshot column where the user can see the comparison of latest snapshot and compare it with the pervious snapshot. The prev snapshot is another measure that i calculate in SQL using a windows function and upload it Power BI.

The user is looking for a capability for selecting any 2 snapshot  and the resultant table  (table 2)

please let me know if i need to provide more information.

thanks. 

Table 2 

	snapshot	total_forecasted_sales
	01/04/2024 00:00	11183 (J3)
	04/03/2024 00:00	90000 (J4)
Change in sales		=+J4-J3	78817
%age_change		=+(J4-J3)/J3	7.047929894

Table 1

ITEM_ID	snapshot	salesweek	total_forecasted_sales
SKUA	01/04/2024 00:00	01/04/2024 00:00	11183
SKUA	25/03/2024 00:00	01/04/2024 00:00	11183
SKUA	18/03/2024 00:00	01/04/2024 00:00	41200
SKUA	18/03/2024 00:00	01/04/2024 00:00	17349
SKUA	11/03/2024 00:00	01/04/2024 00:00	23543
SKUA	04/03/2024 00:00	01/04/2024 00:00	5886
SKUA	26/02/2024 00:00	01/04/2024 00:00	36110
SKUA	19/02/2024 00:00	01/04/2024 00:00	26486
SKUA	19/02/2024 00:00	01/04/2024 00:00	33504
SKUA	12/02/2024 00:00	01/04/2024 00:00	26486
SKUA	05/02/2024 00:00	01/04/2024 00:00	26486
SKUA	29/01/2024 00:00	01/04/2024 00:00	50029
SKUA	22/01/2024 00:00	01/04/2024 00:00	26486
SKUA	15/01/2024 00:00	01/04/2024 00:00	139050
SKUA	08/01/2024 00:00	01/01/2024 00:00	172010
SKUA	08/01/2024 00:00	01/04/2024 00:00	106283
SKUA	08/01/2024 00:00	01/05/2024 00:00	107598

[lbendlin]

2. There is no need for separate tables. All you need is a column that identifies the snapshot.