Forum Discussion
Dynamic Calculation Different Period
Hi everyone,
I'm facing a Power BI/DAX issue and would appreciate any suggestions.
Data Model
I have:
- FactSales (transaction table)
- DimDateTable1 (Date table for Period A)
- DimDateTable2 (Date table for Period B)
I intentionally use two separate date tables because I want users to compare two completely different date ranges independently.
Scenario
For example:
- Period A (DimDateTable1): December 1–3, 2025
- Period B (DimDateTable2): December 4–8, 2025
I have the following measures:
- YtdNet (A) → calculated based on Period A
- YtdSalesSQL (B) → calculated based on Period B
- YtdSalesPostgre (C) → calculated based on Period B
My goal is to create another measure:
YtdFinal (D) = YtdNet (A) + YtdSalesSQL (B) + YtdSalesPostgre (C)
The Problem
The issue is that YtdNet (A) should always use the date selection from DimDateTable1 (December 1–3, 2025), while YtdSalesSQL (B) and YtdSalesPostgre (C) should use the date selection from DimDateTable2 (December 4–8, 2025).
When I display YtdFinal in another matrix visual filtered by Period B, YtdNet (A) is no longer evaluated using Period A. Instead, it is affected by the current filter context, causing the total to be incorrect.
My Question
How can I correctly calculate:
YtdFinal = Measure A (using Date Range A) + Measure B (using Date Range B) + Measure C (using Date Range B)
while each measure keeps its own independent date filter?
Is using two separate date tables the right approach for this scenario, or is there a better design pattern for comparing two independent date ranges in the same report?
Here is the sample illustration condition right now
Any advice or recommended DAX pattern would be greatly appreciated.
Thanks in advance!
Thankyou powerbidev123 for Addressing the issue.
Hi chrisyan_manalu ,Thank you for reaching out to Microsoft Fabric Community Forum,Below are the dax measure you can try to resolve your issue.
Using two separate date tables is the correct design if the requirement is to compare two completely independent date selections. However, the issue occurs because when YtdFinal is evaluated in a visual filtered by Period B, the filter context from DimDateTable2 propagates to the fact table, and your YtdNet measure is no longer isolated to Period A. The solution is to make each measure explicitly control which date table affects the fact table.
YtdNet = VAR SelectedDates = VALUES ( DimDateTable1[Date] ) RETURN CALCULATE ( [NetMeasure], REMOVEFILTERS ( DimDateTable2 ), TREATAS ( SelectedDates, FactSales[Date] ) )YtdSalesSQL = VAR SelectedDates = VALUES ( DimDateTable2[Date] ) RETURN CALCULATE ( [SalesSQL], REMOVEFILTERS ( DimDateTable1 ), TREATAS ( SelectedDates, FactSales[Date] ) )YtdSalesPostgre = VAR SelectedDates = VALUES ( DimDateTable2[Date] ) RETURN CALCULATE ( [SalesPostgre], REMOVEFILTERS ( DimDateTable1 ), TREATAS ( SelectedDates, FactSales[Date] ) )YtdFinal = [YtdNet] + [YtdSalesSQL] + [YtdSalesPostgre]Thanks & Regards,
Chaithanya.
7 Replies
Use:
- one real Date table related to FactSales
- two disconnected date selector tables for Period A and Period B
- in each measure, explicitly remove current date context and apply the correct period inside CALCULATE
This is the cleanest and most reliable pattern for comparing two independent date ranges in the same report.
Model design
Keep this relationship
- FactSales[Date] → DimDate[Date] (active relationship)
Make these tables disconnected
- DimDateTable1 = Period A selector
- DimDateTable2 = Period B selector
DimDateTable1 and DimDateTable2 should not be related to FactSales.
They are only slicer tables.
First, keep your base measures date-independent:Net Amount = SUM(FactSales[NetAmount])
Sales SQL Amount = SUM(FactSales[SalesSQL])
Sales Postgre Amount = SUM(FactSales[SalesPostgre])
Now create the final comparison measures like this:
YtdNet_A =
VAR EndDateA =
MAX(DimDateTable1[Date])
RETURN
CALCULATE(
[Net Amount],
REMOVEFILTERS(DimDate),
REMOVEFILTERS(DimDateTable2),
DATESBETWEEN(
DimDate[Date],
DATE(YEAR(EndDateA), 1, 1),
EndDateA
)
)
YtdSalesSQL_B = VAR EndDateB =MAX(DimDateTable2[Date])
RETURN
CALCULATE( [Sales SQL Amount], REMOVEFILTERS(DimDate), REMOVEFILTERS(DimDateTable1),
DATESBETWEEN( DimDate[Date], DATE(YEAR(EndDateB), 1, 1), EndDateB )
)
YtdSalesPostgre_B = VAR EndDateB = MAX(DimDateTable2[Date]) RETURN CALCULATE( [Sales Postgre Amount], REMOVEFILTERS(DimDate), REMOVEFILTERS(DimDateTable1), DATESBETWEEN( DimDate[Date], DATE(YEAR(EndDateB), 1, 1), EndDateB ) )
YtdFinal = [YtdNet_A] + [YtdSalesSQL_B] + [YtdSalesPostgre_B]Why this works
YtdFinal can be shown anywhere — even in a visual affected by Period B — because:
- YtdNet_A always ignores current date context and uses only DimDateTable1
- YtdSalesSQL_B and YtdSalesPostgre_B always ignore current date context and use only DimDateTable2
So each measure keeps its own independent date filter.
Thank you for taking the time to look into my issue and suggest a solution. I really appreciate it.However, after trying your approach, the result is still not what I expected. I think the issue might be that DATESBETWEEN is not capturing the currently selected date range correctly, which causes the returned value to be much larger than expected.
The main challenge is how to make the measure always use the selected period from Date Range A for YtdNet (A), while still adding it to Measure B and Measure C, which are calculated based on Date Range B.
In other words, I need YtdNet (A) to always respect the user's selection in Date Range A, regardless of the filter context coming from Date Range B. Is there a DAX pattern that allows a measure to preserve the filter context from one disconnected date table while combining it with measures that use another date table?Thankyou powerbidev123 for Addressing the issue.
Hi chrisyan_manalu ,Thank you for reaching out to Microsoft Fabric Community Forum,Below are the dax measure you can try to resolve your issue.
Using two separate date tables is the correct design if the requirement is to compare two completely independent date selections. However, the issue occurs because when YtdFinal is evaluated in a visual filtered by Period B, the filter context from DimDateTable2 propagates to the fact table, and your YtdNet measure is no longer isolated to Period A. The solution is to make each measure explicitly control which date table affects the fact table.
YtdNet = VAR SelectedDates = VALUES ( DimDateTable1[Date] ) RETURN CALCULATE ( [NetMeasure], REMOVEFILTERS ( DimDateTable2 ), TREATAS ( SelectedDates, FactSales[Date] ) )YtdSalesSQL = VAR SelectedDates = VALUES ( DimDateTable2[Date] ) RETURN CALCULATE ( [SalesSQL], REMOVEFILTERS ( DimDateTable1 ), TREATAS ( SelectedDates, FactSales[Date] ) )YtdSalesPostgre = VAR SelectedDates = VALUES ( DimDateTable2[Date] ) RETURN CALCULATE ( [SalesPostgre], REMOVEFILTERS ( DimDateTable1 ), TREATAS ( SelectedDates, FactSales[Date] ) )YtdFinal = [YtdNet] + [YtdSalesSQL] + [YtdSalesPostgre]Thanks & Regards,
Chaithanya.
Hi,
Please share the download link of the PBI file and show the problem there clearly. Please also share the downliad link of the Excel file shown in the screenshot.
Hi,
Please share the download link of the PBI file and show the problem there clearly. Please also share the downliad link of the Excel file shown in the screenshot.
Thankyou Ashish_Mathur , powerbidev123 for Addressing the issue.
Hi chrisyan_manalu ,Thank you for reaching out to Microsoft Fabric Community Forum,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,Chaithanya
Thankyou @Ashish_Mathur , @powerbidev123 for Addressing the issue.
Hi @chrisyan_manalu ,Thank you for reaching out to Microsoft Fabric Community Forum,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,Chaithanya
