Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
sandy_PBI
Regular Visitor

Snapshot Comparison based on dynamic date selection

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? 

 

sandy_PBI_1-1712203331461.png

 

 

appreicate any inputs.

thanks,

 

1 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.

View solution in original post

7 REPLIES 7
AnalyticsWizard
Solution Supplier
Solution Supplier

@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.

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. 

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
Regular Visitor

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

thanks,

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...

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 

 snapshottotal_forecasted_sales 
 01/04/2024 00:0011183 (J3) 
 04/03/2024 00:0090000 (J4) 
Change in sales =+J4-J378817
%age_change =+(J4-J3)/J37.047929894

 

Table 1

ITEM_IDsnapshotsalesweektotal_forecasted_sales
SKUA01/04/2024 00:0001/04/2024 00:0011183
SKUA25/03/2024 00:0001/04/2024 00:0011183
SKUA18/03/2024 00:0001/04/2024 00:0041200
SKUA18/03/2024 00:0001/04/2024 00:0017349
SKUA11/03/2024 00:0001/04/2024 00:0023543
SKUA04/03/2024 00:0001/04/2024 00:005886
SKUA26/02/2024 00:0001/04/2024 00:0036110
SKUA19/02/2024 00:0001/04/2024 00:0026486
SKUA19/02/2024 00:0001/04/2024 00:0033504
SKUA12/02/2024 00:0001/04/2024 00:0026486
SKUA05/02/2024 00:0001/04/2024 00:0026486
SKUA29/01/2024 00:0001/04/2024 00:0050029
SKUA22/01/2024 00:0001/04/2024 00:0026486
SKUA15/01/2024 00:0001/04/2024 00:00139050
SKUA08/01/2024 00:0001/01/2024 00:00172010
SKUA08/01/2024 00:0001/04/2024 00:00106283
SKUA08/01/2024 00:0001/05/2024 00:00107598
lbendlin
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.