Hi, I have 2 tables :- PRODUCT_VERSION :Product_idProduct_versionStart_date End_dateA101/01/2024 31/08/2024A201/09/2024 30/04/2025A301/05/2025 - PRODUCT_SALES :Product_idSalesSales_dateA618/01/2024A2421/09/2024A801/03/2025I want to calculate the sales for each product version in the PRODUCT_VERSION table.I join the 2 tables on product_id and I create a measure : CALCULATE (sales,DATESBETWEEN(Sales_date,Start_date,End_date))But it doesn't work, do you have an idea ?

Hello scolvin, You’re running into a classic period join problem in DAX. The issue is that DATESBETWEEN expects a date column from a Date table, not row‑level start/end dates from another table. That’s why your original measure doesn’t work.Microsoft Documentation:https://learn.microsoft.com/en-us/dax/calculate-function-dax?utm_https://learn.microsoft.com/en-us/dax/filter-function-dax?utm_https://learn.microsoft.com/en-us/dax/datesbetween-function-dax?utm_Correct DAX PatternSince your PRODUCT_VERSION table has row‑level start and end dates, use FILTER:Sales by Version = CALCULATE ( SUM ( PRODUCT_SALES[Sales] ), FILTER ( PRODUCT_SALES, PRODUCT_SALES[Sales_date] >= PRODUCT_VERSION[Start_date] && PRODUCT_SALES[Sales_date] <= PRODUCT_VERSION[End_date] ) )This ensures that each product version row filters sales correctly.Alternative with a Date TableIf you set up a proper Date table in Fabric/Power BI and relate it to PRODUCT_SALES[Sales_date], you can use:Sales by Version = CALCULATE ( SUM ( PRODUCT_SALES[Sales] ), DATESBETWEEN ( 'Date'[Date], PRODUCT_VERSION[Start_date], PRODUCT_VERSION[End_date] ) ) This works only if:A Date table exists and is related to sales.The measure is evaluated in the context of a single product version row.Step‑by‑Step Example with Your DataPRODUCT_VERSIONProduct_id Product_version Start_date End_date A101/01/202431/08/2024A201/09/202430/04/2025A301/05/2025(open) PRODUCT_SALESProduct_id Sales Sales_date A618/01/2024A2421/09/2024A801/05/2025 Matching Sales to VersionsVersion 1 (01/01/2024 → 31/08/2024)Sales on 18/01/2024 → falls in this range.Total = 6.Version 2 (01/09/2024 → 30/04/2025)Sales on 21/09/2024 → falls in this range.Total = 24.Version 3 (01/05/2025 → open)Sales on 01/05/2025 → falls in this range.Total = 8.Final ResultsProduct_version Sales_total 1622438 TakeawayUse FILTER when comparing row‑level start/end dates.Use DATESBETWEEN only with a proper Date table.In Fabric, you can either solve it at the semantic model level (DAX) or at the data prep level (SQL range join).This approach ensures each product version correctly aggregates the sales that fall within its defined period.

Forum Discussion

scolvin

Helper I

7 months ago

Solved

measure with period join

Hi,

I have 2 tables :

- PRODUCT_VERSION :

Product_id	Product_version	Start_date	End_date
A	1	01/01/2024	31/08/2024
A	2	01/09/2024	30/04/2025
A	3	01/05/2025

- PRODUCT_SALES :

Product_id	Sales	Sales_date
A	6	18/01/2024
A	24	21/09/2024
A	8	01/03/2025

I want to calculate the sales for each product version in the PRODUCT_VERSION table.
I join the 2 tables on product_id and I create a measure : CALCULATE (sales,DATESBETWEEN(Sales_date,Start_date,End_date))

But it doesn't work, do you have an idea ?

datesbetween

Olufemi7
7 months ago
Hello scolvin,

You’re running into a classic period join problem in DAX. The issue is that DATESBETWEEN expects a date column from a Date table, not row‑level start/end dates from another table. That’s why your original measure doesn’t work.

Microsoft Documentation:

https://learn.microsoft.com/en-us/dax/calculate-function-dax?utm_

https://learn.microsoft.com/en-us/dax/filter-function-dax?utm_

https://learn.microsoft.com/en-us/dax/datesbetween-function-dax?utm_
Correct DAX Pattern
Since your PRODUCT_VERSION table has row‑level start and end dates, use FILTER:
Sales by Version = CALCULATE ( SUM ( PRODUCT_SALES[Sales] ), FILTER ( PRODUCT_SALES, PRODUCT_SALES[Sales_date] >= PRODUCT_VERSION[Start_date] && PRODUCT_SALES[Sales_date] <= PRODUCT_VERSION[End_date] ) )

This ensures that each product version row filters sales correctly.

Alternative with a Date Table
If you set up a proper Date table in Fabric/Power BI and relate it to PRODUCT_SALES[Sales_date], you can use:

Sales by Version = CALCULATE ( SUM ( PRODUCT_SALES[Sales] ), DATESBETWEEN ( 'Date'[Date], PRODUCT_VERSION[Start_date], PRODUCT_VERSION[End_date] ) )

This works only if:
A Date table exists and is related to sales.
The measure is evaluated in the context of a single product version row.

Step‑by‑Step Example with Your Data
PRODUCT_VERSION
Product_id Product_version Start_date End_date
A 1 01/01/2024 31/08/2024
A 2 01/09/2024 30/04/2025
A 3 01/05/2025 (open)

PRODUCT_SALES
Product_id Sales Sales_date
A 6 18/01/2024
A 24 21/09/2024
A 8 01/05/2025

Matching Sales to Versions
Version 1 (01/01/2024 → 31/08/2024)
Sales on 18/01/2024 → falls in this range.
Total = 6.
Version 2 (01/09/2024 → 30/04/2025)
Sales on 21/09/2024 → falls in this range.
Total = 24.
Version 3 (01/05/2025 → open)
Sales on 01/05/2025 → falls in this range.
Total = 8.

Final Results
Product_version Sales_total
1 6
2 24
3 8

Takeaway
Use FILTER when comparing row‑level start/end dates.
Use DATESBETWEEN only with a proper Date table.
In Fabric, you can either solve it at the semantic model level (DAX) or at the data prep level (SQL range join).

This approach ensures each product version correctly aggregates the sales that fall within its defined period.

5 Replies

scolvin
Helper I
7 months ago
The result must be

Product_id Product_version Start_date End_date Sales
A 1 01/01/2024 31/08/2024 6
A 2 01/09/2024 30/04/2025 32
A 3 01/05/2025

Thank you
- FreemanZ
  Super User
  7 months ago
  hi scolvin ，
  
  Not sure if i fully get you, you can try to add a calculated column in version table like:
  Sales = SUMX( FILTER( sales, sales[Product_id]=[Product_id] && sales[Sales_date]>=[Start_date] && sales[Sales_date]<=[End_date] ), sales[Sales] )
  
  it works like:

cengizhanarslan

Super User

7 months ago

Please try the formula below:

Sales by Version =
VAR _Start = SELECTEDVALUE ( PRODUCT_VERSION[Start_date] )
VAR _End   = SELECTEDVALUE ( PRODUCT_VERSION[End_date] )
RETURN
CALCULATE (
    SUM ( PRODUCT_SALES[Sales] ),
    FILTER (
        PRODUCT_SALES,
        PRODUCT_SALES[Sales_date] >= _Start
            && PRODUCT_SALES[Sales_date] <= _End
    )
)

Olufemi7
Super User
7 months ago
Hello scolvin,

You’re running into a classic period join problem in DAX. The issue is that DATESBETWEEN expects a date column from a Date table, not row‑level start/end dates from another table. That’s why your original measure doesn’t work.

Microsoft Documentation:

https://learn.microsoft.com/en-us/dax/calculate-function-dax?utm_

https://learn.microsoft.com/en-us/dax/filter-function-dax?utm_

https://learn.microsoft.com/en-us/dax/datesbetween-function-dax?utm_
Correct DAX Pattern
Since your PRODUCT_VERSION table has row‑level start and end dates, use FILTER:
Sales by Version = CALCULATE ( SUM ( PRODUCT_SALES[Sales] ), FILTER ( PRODUCT_SALES, PRODUCT_SALES[Sales_date] >= PRODUCT_VERSION[Start_date] && PRODUCT_SALES[Sales_date] <= PRODUCT_VERSION[End_date] ) )

This ensures that each product version row filters sales correctly.

Alternative with a Date Table
If you set up a proper Date table in Fabric/Power BI and relate it to PRODUCT_SALES[Sales_date], you can use:

Sales by Version = CALCULATE ( SUM ( PRODUCT_SALES[Sales] ), DATESBETWEEN ( 'Date'[Date], PRODUCT_VERSION[Start_date], PRODUCT_VERSION[End_date] ) )

This works only if:
A Date table exists and is related to sales.
The measure is evaluated in the context of a single product version row.

Step‑by‑Step Example with Your Data
PRODUCT_VERSION
Product_id Product_version Start_date End_date
A 1 01/01/2024 31/08/2024
A 2 01/09/2024 30/04/2025
A 3 01/05/2025 (open)

PRODUCT_SALES
Product_id Sales Sales_date
A 6 18/01/2024
A 24 21/09/2024
A 8 01/05/2025

Matching Sales to Versions
Version 1 (01/01/2024 → 31/08/2024)
Sales on 18/01/2024 → falls in this range.
Total = 6.
Version 2 (01/09/2024 → 30/04/2025)
Sales on 21/09/2024 → falls in this range.
Total = 24.
Version 3 (01/05/2025 → open)
Sales on 01/05/2025 → falls in this range.
Total = 8.

Final Results
Product_version Sales_total
1 6
2 24
3 8

Takeaway
Use FILTER when comparing row‑level start/end dates.
Use DATESBETWEEN only with a proper Date table.
In Fabric, you can either solve it at the semantic model level (DAX) or at the data prep level (SQL range join).

This approach ensures each product version correctly aggregates the sales that fall within its defined period.
scolvin
Helper I
7 months ago
Thank you guys 👍
It's ok with SELECTEDVALUE(Start_date) and SELECTEDVALUE(End_date)

Forum Discussion

measure with period join

Alternative with a Date Table

PRODUCT_VERSION

PRODUCT_SALES

Matching Sales to Versions

Final Results

Takeaway

5 Replies

Alternative with a Date Table

PRODUCT_VERSION

PRODUCT_SALES

Matching Sales to Versions

Final Results

Takeaway

Related content

Previous Period measure

Showing 13 periods

Matrix period showing incorrect total when using previous year measure

Using Measure to Compare Current Period to Previous Period

Period over Period Change Measure